Comments by Mohammad Imran1

Mohammad Imran1 30-Nov-22 8:01am View

I have created mycalendar table


Select * from mycalendar

CALENDAR DATE DAY MONTH
2022-11-29 00:00:00.000 29 11
2022 3
2022-11-30 00:00:00.000 30 11 4 2022 4
2022-12-01 00:00:00.000 1 12 4 2022 5
2022-12-02 00:00:00.000 2 12 4 2022 6
2022-12-03 00:00:00.000 3 12 4 2022 7
2022-12-04 00:00:00.000 4 12 4 2022 1

Mohammad Imran1 30-Nov-22 4:34am View

im using SQL.. and i want to add missing dates .. check my below query


select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
from(
select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut


from CHECKINOUT c
group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
)temp
) attendance
inner join userinfo u on u.USERID = attendance.USERID

Mohammad Imran1 30-Nov-22 1:00am View

send me your solution avoid above i need missing dates those employee absent

Mohammad Imran1 29-Nov-22 5:53am View

Please tell me how can i add missing dates in below query from checkdate column


select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
    select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
    ,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
        from(
            select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
            ,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
            ,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
           
 
            from CHECKINOUT c
            group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
        )temp
) attendance 
inner join userinfo u on u.USERID = attendance.USERID

Mohammad Imran1 29-Nov-22 4:42am View

How can i add missing dates in below query

select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
    select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
    ,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
        from(
              select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
            --,(select temp.USERID, dateadd(day,1, CHECKTIME cinout where temp.USERID = c.USERID)) CHEC2
            ,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
            ,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
            from CHECKINOUT c
            group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
        )temp
) attendance 
inner join  userinfo u on u.USERID = attendance.userid where u.USERID = 77 <code>

Mohammad Imran1 29-Nov-22 2:00am View

Ok got it with below query but problem is im unable to get absent record, dates are missing when employ was not present how to add missing dates in below query please help. dates missing

Umair	77	1	2022-11-16	2022-11-16 09:41:25.000	2022-11-16 18:45:46 9:4
Umair	77	1	2022-11-21	2022-11-21 09:29:29.000	2022-11-21 19:00:33	9:31
Umair	77	1	2022-11-25	2022-11-25 09:31:00.000	NULL	NULL
Umair	77	1	2022-11-26	2022-11-26 10:42:55.000	NULL	NULL

I want below result with all missing dates

Umair	77	1	2022-11-16	2022-11-16 09:41:25.000	2022-11-16 18:45:46 9:4
Umair	77	1	2022-11-16	2022-11-17 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-18 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-19 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-20 NULL   NULL NULL
Umair	77	1	2022-11-21	2022-11-21 09:29:29.000	2022-11-21 19:00:33	9:31
Umair	77	1	2022-11-16	2022-11-22 NULL   NULL NULL
Umair	77	1	2022-11-16	2022-11-23 NULL   NULL NULL	NULL

Check my below query

select u.NAME,u.BADGENUMBER, attendance.SENSORID, attendance.CHECKDate, attendance.CheckIn ,attendance.CheckOut
,cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) / 60 as varchar) + ':' + cast(DATEDIFF(n, attendance.CheckIn, attendance.checkout) % 60 as varchar) as Minutes
from (
    select temp.USERID, temp.SENSORID, temp.CHECKDate ,temp.CheckIn
    ,case when temp.COut = temp.CheckIn then null when temp.CheckIn is null then null else temp.COut end as CheckOut
        from(
            select c.USERID, c.SENSORID, convert(date, CHECKTIME) CHECKDate
            ,(select min(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime >= dateadd(hour, 6, convert(datetime, convert(date, c.CheckTime)))) CheckIn
            ,(select max(CHECKTIME) from CHECKINOUT cinout where cinout.USERID = c.USERID and cinout.SENSORID = c.SENSORID and cinout.CheckTime <= dateadd(hour, 29, convert(datetime, convert(date, c.CheckTime)))) COut
           
 
            from CHECKINOUT c
            group by c.USERID, c.SENSORID, convert(date, CHECKTIME), datename(DW,checktime)
        )temp
) attendance 
inner join userinfo u on u.USERID = attendance.USERID where u.USERID =77

Mohammad Imran1 21-Nov-22 7:13am View

I want to grab from CHECKTIME column through SQL, because checktype data is incorrect and i want to use only time to first checkin and last checkout. So if you know about SQL please tell me

Mohammad Imran1 21-Nov-22 4:59am View

Ok but how to do in sql and separate column check in and check out..

Mohammad Imran1 4-Apr-19 6:03am View

any update anyone can help please???

Mohammad Imran1 1-Apr-19 8:14am View

This is my second file saving code .

private void CreateAttachment(string strFile)
{
SqlDataAdapter objAdapter = new SqlDataAdapter(strQuery_AllAttachments_AllFields, objConn);
objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
DataTable objTable = new DataTable();
FileStream objFileStream = new FileStream(strFile, FileMode.Open, FileAccess.Read);
int intLength = Convert.ToInt32(objFileStream.Length);
byte[] objData;

objData = new byte[intLength];
DataRow objRow;
string[] strPath = strFile.Split(Convert.ToChar(@"\"));
objAdapter.Fill(objTable);

objFileStream.Read(objData, 0, intLength);
objFileStream.Close();

objRow = objTable.NewRow();
objRow["FileName"] = strPath[strPath.Length - 1]; //clip the full path - we just want last part!
objRow["FileSize"] = intLength / 5008; // KB instead of bytes
objRow["CustData"] = objData; //our file
objRow["CustName"] = comboBox1.Text.ToString();
objRow["CaseName"] = textBox1.Text.ToString();
objRow["Remarks"] = textBox3.Text.ToString();
objRow["Date"] = dateTimePicker1.Value.ToShortDateString();
objTable.Rows.Add(objRow); //add our new record
objAdapter.Update(objTable);

}

Mohammad Imran1 1-Apr-19 5:49am View

This is not full code, i'm getting space/long code error when tried to upload whole code. Kindly let me know the full solution i'm new in this field.

Mohammad Imran1 22-Aug-17 1:03am View

Not working at all.. after changed data type datetime..

private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{

if (e.ColumnIndex == 6)
{

var mydatetime = DateTime.Parse(dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());

if (mydatetime.Hour > 9 && mydatetime.Minute > 30)
{
e.CellStyle.BackColor = Color.Yellow;

}
}
}

Mohammad Imran1 22-Aug-17 0:46am View

I have add below code ..but cell not change color.. my datatype is datetime..

private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{

if (e.ColumnIndex == 6)
{

var mydatetime = DateTime.Parse(dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString());

if (mydatetime.Hour > 9 && mydatetime.Minute > 30)
{
e.CellStyle.BackColor = Color.Yellow;

}
}
}

Mohammad Imran1 22-Aug-17 0:32am View

Its not working at all.. My time data type of column is datetime..

Mohammad Imran1 21-Aug-17 9:30am View

Ok boss..Let me check with that

Mohammad Imran1 21-Aug-17 9:29am View

Sorry i tried but still the same. Let me tell you that i have CLOCK IN column in database and data type is time.

Kindly let me know i'm new one..i don't know how to part time from this query.

Waiting
Thanks

Mohammad Imran1 21-Aug-17 9:22am View

Remember i have Clock In column data type time in database

Mohammad Imran1 21-Aug-17 8:58am View

No i didn't see..below code is not working..its giving error .

Error 1 The best overloaded method match for 'System.DateTime.Parse(string)' has some invalid arguments

2,Error 2 Argument 1: cannot convert from 'object' to 'string'
{

if (e.ColumnIndex == 5)
{
var mydatetime = DateTime.Parse(myDataGridView.Rows[e.RowIndex].Cells[e.ColumnIndex].Value);

if (mydatetime.Hour > 9 && mydatetime.Minute > 30)
{
e.CellStyle.BackColor = Color.Yellow;
}
}

Mohammad Imran1 21-Aug-17 8:48am View

Hello any buddy is there?

Mohammad Imran1 21-Aug-17 5:47am View

Ok Thanks... What about above query its giving error...
Error 1 The best overloaded method match for 'System.DateTime.Parse(string)' has some invalid arguments

2,Error 2 Argument 1: cannot convert from 'object' to 'string'

Mohammad Imran1 21-Aug-17 5:17am View

please also let me know below query i want to count total late if time exceeded 9:30

select ([Clock In]) from tbl_register where name = 'Kashif' and ([Clock In]) > '9:30'

Mohammad Imran1 21-Aug-17 5:16am View

1, Error 1 The best overloaded method match for 'System.DateTime.Parse(string)' has some invalid arguments

2,Error 2 Argument 1: cannot convert from 'object' to 'string'

Mohammad Imran1 6-Feb-16 8:10am View

actually i have added crystal report on my windows based project in C#.. its giving me an error

(
Error 2 'PointOfSale_Imran.CrystalReport123' does not contain a definition for 'ReportDefinition' and no extension method 'ReportDefinition' accepting a first argument of type 'PointOfSale_Imran.CrystalReport123' could be found (are you missing a using directive or an assembly reference?) C:\Users\Administrator\Desktop\IMRAN-POINTOFSALE\PointOfSale-Imran\PointOfSale-Imran\CrystalReport123.cs 66 29 PointOfSale-Imran
)

I dont understand that.. i'm new one in programming.. please resolve this as i need to add report in my project ASAP..

Thanks and Waiting for your positive answer..

Comments by Mohammad Imran1 (Top 23 by date)