public static List <SLABLL> GetSLAByDateDeposit(Guid WarehouseId, DateTime DateDeposit)
{
List <SLABLL> list = null;
string strSql = "spSLARPT";
SqlDataReader reader;
SqlParameter[] arPar = new SqlParameter[2];
arPar[0] = new SqlParameter("@WarehouseId", SqlDbType.UniqueIdentifier);
arPar[0].Value = WarehouseId;
arPar[1] = new SqlParameter("@DepositDate", SqlDbType.DateTime);
arPar[1].Value = DateDeposit;
SqlConnection conn = null;
try
{
conn = Connection.getConnection();
reader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, strSql, arPar);
if (reader.HasRows)
{
list = new List <SLABLL>();
while (reader.Read())
{
SLABLL obj = new SLABLL();
if (reader["VoucherNo"] != DBNull.Value)
{
obj.objVoucher.VoucherNo = reader["VoucherNo"].ToString();
}
if (reader["ClientId"] != DBNull.Value)
{
obj.objGRN.ClientId = new Guid(reader["ClientId"].ToString());
}
if (reader["PlateNumber"] != DBNull.Value)
{
obj.objDriver.PlateNumber = reader["PlateNumber"].ToString();
}
if (reader["TrailerPlateNumber"] != DBNull.Value)
{
obj.objDriver.TrailerPlateNumber = reader["TrailerPlateNumber"].ToString();
}
if (reader["TotalNumberOfBags"] != DBNull.Value)
{
obj.objUnloading.TotalNumberOfBags = int.Parse(reader["TotalNumberOfBags"].ToString());
}
if (reader["ArrivalDate"] != DBNull.Value)
{
obj.objArrival.DateTimeRecived = DateTime.Parse(reader["ArrivalDate"].ToString());
}
if (reader["ArrivalDateSystem"] != DBNull.Value)
{
obj.objArrival.CreatedTimestamp = DateTime.Parse(reader["ArrivalDateSystem"].ToString());
}
if (reader["SampledDate"] != DBNull.Value)
{
obj.objSampling.GeneratedTimeStamp = DateTime.Parse(reader["SampledDate"].ToString());
}
if (reader["SampledDateSystem"] != DBNull.Value)
{
obj.objSampling.CreatedTimestamp = DateTime.Parse(reader["SampledDateSystem"].ToString());
}
if (reader["SamplingResultRecivedDate"] != DBNull.Value)
{
obj.objSamplingResult.ResultReceivedDateTime = DateTime.Parse(reader["SamplingResultRecivedDate"].ToString());
}
if (reader["SamplingResultRecivedDate"] != DBNull.Value)
{
obj.objSamplingResult.CreatedTimeStamp = DateTime.Parse(reader["SamplingResultRecivedDate"].ToString());
}
if (reader["SamplingResultStatus"] != DBNull.Value)
{
obj.objSamplingResult.Status = (SamplingResultStatus)int.Parse(reader["TotalNumberOfBags"].ToString());
}
if (reader["CodingDate"] != DBNull.Value)
{
obj.objGrading.DateCoded = DateTime.Parse(reader["CodingDate"].ToString());
}
if (reader["CodingDateSystem"] != DBNull.Value)
{
obj.objGrading.CreatedTimestamp = DateTime.Parse(reader["CodingDateSystem"].ToString());
}
if (reader["GradeRecivedTimestamp"] != DBNull.Value)
{
obj.objGradingResult.GradeRecivedTimeStamp = DateTime.Parse(reader["GradeRecivedTimestamp"].ToString());
}
if (reader["GradeRecivedTimestampSystem"] != DBNull.Value)
{
obj.objGradingResult.CreatedTimestamp = DateTime.Parse(reader["GradeRecivedTimestampSystem"].ToString());
}
if (reader["ClientAcceptanceTimeStamp"] != DBNull.Value)
{
obj.objGradingResult.ClientAcceptanceTimeStamp = DateTime.Parse(reader["ClientAcceptanceTimeStamp"].ToString());
}
else
{
obj.objGradingResult.ClientAcceptanceTimeStamp = DateTime.Parse("1/1/0001");
}
if (reader["GradingResultStatus"] != DBNull.Value)
{
obj.objGradingResult.Status = (GradingResultStatus)int.Parse(reader["GradingResultStatus"].ToString());
}
if (reader["DateDeposited"] != DBNull.Value)
{
obj.objUnloading.DateDeposited = DateTime.Parse(reader["DateDeposited"].ToString());
}
if (reader["DateDepositedSystem"] != DBNull.Value)
{
obj.objUnloading.CreatedTimestamp = DateTime.Parse(reader["DateDepositedSystem"].ToString());
}
if (reader["DateWeighed"] != DBNull.Value)
{
obj.objScaling.DateWeighed = DateTime.Parse(reader["DateWeighed"].ToString());
}
if (reader["DateWeighedSystem"] != DBNull.Value)
{
obj.objScaling.CreatedTimestamp = DateTime.Parse(reader["DateWeighedSystem"].ToString());
}
if (reader["GRN_Number"] != DBNull.Value)
{
if (reader["GRNCreatedDate"] != DBNull.Value)
{
obj.objGRN.GRNCreatedDate = DateTime.Parse(reader["GRNCreatedDate"].ToString());
}
if (reader["GRNCreatedDateSystem"] != DBNull.Value)
{
obj.objGRN.CreatedTimestamp = DateTime.Parse(reader["GRNCreatedDateSystem"].ToString());
}
if (reader["ClientAccepted"] != DBNull.Value)
{
obj.objGRN.ClientAccepted = Boolean.Parse(reader["ClientAccepted"].ToString());
}
if (reader["ClientAcceptedTimeStamp"] != DBNull.Value)
{
obj.objGRN.ClientAcceptedTimeStamp = DateTime.Parse(reader["ClientAcceptedTimeStamp"].ToString());
}
if (reader["ManagerApprovedDateTime"] != DBNull.Value)
{
obj.objGRN.ManagerApprovedDateTime = DateTime.Parse(reader["ManagerApprovedDateTime"].ToString());
}
if (reader["ManagerApprovedDateTimeSystem"] != DBNull.Value)
{
obj.objGRN.ApprovedTimeStamp = DateTime.Parse(reader["ManagerApprovedDateTimeSystem"].ToString());
}
if (reader["GRNStatus"] != DBNull.Value)
{
obj.objGRN.Status = int.Parse(reader["GRNStatus"].ToString());
}
if (reader["OriginalQuantity"] != DBNull.Value)
{
obj.objGRN.OriginalQuantity = float.Parse(reader["OriginalQuantity"].ToString());
}
if (reader["GRN_Number"] != DBNull.Value)
{
obj.objGRN.GRN_Number = reader["GRN_Number"].ToString();
}
}
list.Add(obj);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
return(list);
}
private void getData(DateTime from)
{
SLABLL o = new SLABLL();
List <SLABLL> list = null;
Guid Id = UserBLL.GetCurrentWarehouse();
list = o.GetSLA(Id, from, 0);
if (list != null)
{
var q = (from l in list
orderby l.objArrival.DateTimeRecived ascending
select new
{
VoucherNo = l.objVoucher.VoucherNo,
Client = ClientBLL.GetClinetNameById(l.objGRN.ClientId),
PlateNumber = l.objDriver.PlateNumber,
TrailerPlateNumber = l.objDriver.TrailerPlateNumber,
TotalNumberOfBags = l.objUnloading.TotalNumberOfBags,
ArrivalDate = l.objArrival.DateTimeRecived.ToShortDateString(),
//ArrivalDateSystem = l.objArrival.CreatedTimestamp ,
SampledDate = l.objSampling.GeneratedTimeStamp.ToShortDateString(),
ArrivalToSample = (l.objSampling.GeneratedTimeStamp.Date.Subtract(l.objArrival.DateTimeRecived.Date)).Days,
//Sampling Result
//SamplingResultDate = (l.objSamplingResult.CreatedTimestamp).ToShortDateString(),
SamplingResultDate = l.objSamplingResult.ResultReceivedDateTime.ToShortDateString(),
SamplingToResult = (l.objSamplingResult.ResultReceivedDateTime.Date.Subtract(l.objSampling.GeneratedTimeStamp.Date)).Days,
//Coding
CodingDate = l.objGrading.DateCoded.ToShortDateString(),
SamplingResultToCode = (l.objGrading.DateCoded.Date.Subtract(l.objSamplingResult.ResultReceivedDateTime.Date)).Days,
//Grading Result
GradeRecivedDate = l.objGradingResult.CreatedTimestamp.ToShortDateString(),
GradeToResult = (l.objGradingResult.GradeRecivedTimeStamp.Date.Subtract(l.objGrading.DateCoded.Date)).Days,
// GR to Client Acceptance
GradeClientResponse = ((DateTime)l.objGradingResult.ClientAcceptanceTimeStamp).ToShortDateString(),
GraderesultStatus = l.objGradingResult.Status,
GradingResultToCleintAcceptance = (((DateTime)l.objGradingResult.ClientAcceptanceTimeStamp).Date.Subtract(l.objGradingResult.GradeRecivedTimeStamp.Date)).Days,
GradingRecivedResultStatus = l.objGradingResult.Status,
GradeResultToClientresponse = (((DateTime)l.objGradingResult.ClientAcceptanceTimeStamp).Date.Subtract(l.objGradingResult.GradeRecivedTimeStamp.Date)).Days,
//Unloading
DateDeposited = l.objUnloading.DateDeposited.ToShortDateString(),
GradeClientResponsetoUnloading = l.objUnloading.DateDeposited.Date.Subtract(((DateTime)l.objGradingResult.ClientAcceptanceTimeStamp).Date).Days,
NoBags = l.objUnloading.TotalNumberOfBags,
//Scaling
DateWeighed = l.objScaling.DateWeighed.ToShortDateString(),
GradeClientResponsetoScaling = l.objScaling.DateWeighed.Date.Subtract(((DateTime)l.objGradingResult.ClientAcceptanceTimeStamp).Date).Days,
//GRN creation
GRN_Number = l.objGRN.GRN_Number,
GRNCreatedDate = l.objGRN.CreatedTimestamp.ToShortDateString(),
ScalingToGRN = l.objGRN.CreatedTimestamp.Date.Subtract(l.objScaling.DateWeighed.Date).Days,
//GRN Acceptance
GRNCleintResponse = l.objGRN.ClientAcceptedTimeStamp.ToShortDateString(),
GRNToCleintResponse = l.objGRN.ClientAcceptedTimeStamp.Date.Subtract(l.objGRN.CreatedTimestamp.Date).Days,
//Manager Approval
ManagerAppp = l.objGRN.ApprovedTimeStamp.ToShortDateString(),
GRNClientResponseToApproval = l.objGRN.ApprovedTimeStamp.Date.Subtract(l.objGRN.ClientAcceptedTimeStamp.Date).Days,
//From Arrival to GRN
FromArrivalToDeposit = l.objUnloading.DateDeposited.Date.Subtract(l.objArrival.DateTimeRecived.Date).Days,
ArrivalToGRNCreation = l.objGRN.CreatedTimestamp.Date.Subtract(l.objArrival.DateTimeRecived.Date).Days,
ArrivalToClientAccptance = l.objGRN.ClientAcceptedTimeStamp.Date.Subtract(l.objArrival.DateTimeRecived.Date).Days,
ArrivalToGRNApproval = l.objGRN.ApprovedTimeStamp.Date.Subtract(l.objArrival.DateTimeRecived.Date).Days
});
if (q != null)
{
if (q.Count() > 0)
{
str.Append("<table align='center' border='1' bordercolor='#000000' width='99%' class='reporttable1' cellspacing='0' cellpadding='0' style='font-size:10;'>");
str.Append("<tr style='color:#000000; font-weight:bold>' ");
//1
str.Append("<td>S.No");
str.Append("</td>");
//2
str.Append("<td>voucher.No");
str.Append("</td>");
//3
str.Append("<td>Company Name");
str.Append("</td>");
//4
str.Append("<td>Plate No.");
str.Append("</td>");
//5
str.Append("<td>Trailer Plate No.");
str.Append("</td>");
//6
str.Append("<td>No. Bags");
str.Append("</td>");
//7
str.Append("<td>Date of Arrival");
str.Append("</td>");
//8
str.Append("<td>Sampling Date");
str.Append("</td>");
//9
str.Append("<td>Arrival to Sampling ");
str.Append("</td>");
//10
str.Append("<td>Sampling Result Date ");
str.Append("</td>");
//11
str.Append("<td>Sampling To Sampling Result ");
str.Append("</td>");
//12
str.Append("<td>Date Coded ");
str.Append("</td>");
//13
str.Append("<td>Sampling Result To Code ");
str.Append("</td>");
//14
str.Append("<td>Grade Recieved Date ");
str.Append("</td>");
//15
str.Append("<td>Grading To Result");
str.Append("</td>");
//16
str.Append("<td>Grading Status");
str.Append("</td>");
//17
str.Append("<td>Grading Result Cleint Respons date");
str.Append("</td>");
//18
str.Append("<td>Grading Result To Cleint Respons");
str.Append("</td>");
//19
str.Append("<td>Date Deposited");
str.Append("</td>");
//20
str.Append("<td>Client Response to Unloading");
str.Append("</td>");
//21
str.Append("<td>DateWeighed");
str.Append("</td>");
//22
str.Append("<td>Client Response to Scaling");
str.Append("</td>");
//23
str.Append("<td>GRN Number ");
str.Append("</td>");
//24
str.Append("<td>GRN Created Date ");
str.Append("</td>");
//25
str.Append("<td>Scaling to GRN ");
str.Append("</td>");
//26
str.Append("<td>GRN Client Response ");
str.Append("</td>");
//27
str.Append("<td>GRN To Cleint Response ");
str.Append("</td>");
//28
str.Append("<td>GRN Approval date ");
str.Append("</td>");
//29
str.Append("<td>Cleint Response to Manger Approval ");
str.Append("</td>");
//29.1
str.Append("<td>Arrival to Deposit");
str.Append("</td>");
//30
str.Append("<td>Arrival To GRN ");
str.Append("</td>");
//31
str.Append("<td>Arrival To GRN Acceptance ");
str.Append("</td>");
//32
str.Append("<td>Arrival To GRN Approval ");
str.Append("</td>");
str.Append("</tr>");
int sno = 0;
foreach (var i in q)
{
sno++;
str.Append("<tr>");
//1
str.Append("<td>");
str.Append(sno.ToString());
str.Append("</td>");
//2
str.Append("<td>");
str.Append(i.VoucherNo.ToString());
str.Append("</td>");
//3
str.Append("<td>");
str.Append(i.Client.ToString());
str.Append("</td>");
//4
str.Append("<td>");
str.Append(i.PlateNumber.ToString());
str.Append("</td>");
//5
str.Append("<td>");
str.Append(i.TrailerPlateNumber.ToString());
str.Append("</td>");
//6
str.Append("<td>");
str.Append(i.TotalNumberOfBags.ToString());
str.Append("</td>");
//7
str.Append("<td>");
str.Append(i.ArrivalDate);
str.Append("</td>");
//8
str.Append("<td>");
str.Append(i.SampledDate);
str.Append("</td>");
//9
str.Append("<td>");
str.Append(i.ArrivalToSample);
str.Append("</td>");
//10
str.Append("<td>");
str.Append(i.SamplingResultDate);
str.Append("</td>");
//11
str.Append("<td>");
str.Append(i.SamplingToResult);
str.Append("</td>");
//12
str.Append("<td>");
str.Append(i.CodingDate);
str.Append("</td>");
//13
str.Append("<td>");
str.Append(i.SamplingResultToCode);
str.Append("</td>");
//14
str.Append("<td>");
str.Append(i.GradeRecivedDate);
str.Append("</td>");
//15
str.Append("<td>");
str.Append(i.GradeToResult);
str.Append("</td>");
//16
str.Append("<td>");
str.Append(i.GraderesultStatus);
str.Append("</td>");
//17
str.Append("<td>");
str.Append(i.GradeClientResponse);
str.Append("</td>");
//18
str.Append("<td>");
str.Append(i.GradingResultToCleintAcceptance);
str.Append("</td>");
//19
str.Append("<td>");
str.Append(i.DateDeposited);
str.Append("</td>");
//20
str.Append("<td>");
str.Append(i.GradeClientResponsetoUnloading);
str.Append("</td>");
//21
str.Append("<td>");
str.Append(i.DateWeighed);
str.Append("</td>");
//22
str.Append("<td>");
str.Append(i.GradeClientResponsetoScaling);
str.Append("</td>");
if (i.GRN_Number != "" && i.GRN_Number != null && DateTime.Parse(i.GRNCreatedDate) != DateTime.Parse("1/1/0001"))
{
//23
str.Append("<td>");
str.Append(i.GRN_Number);
str.Append("</td>");
//24
str.Append("<td>");
str.Append(i.GRNCreatedDate);
str.Append("</td>");
//25
str.Append("<td>");
str.Append(i.ScalingToGRN);
str.Append("</td>");
if (DateTime.Parse(i.GRNCleintResponse) != DateTime.Parse("1/1/0001"))
{
//26
str.Append("<td>");
str.Append(i.GRNCleintResponse);
str.Append("</td>");
//27
str.Append("<td>");
str.Append(i.GRNToCleintResponse);
str.Append("</td>");
}
else
{
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
}
if (DateTime.Parse("1/1/0001") != DateTime.Parse(i.ManagerAppp))
{
//28
str.Append("<td>");
str.Append(i.ManagerAppp);
str.Append("</td>");
//29
str.Append("<td>");
str.Append(i.GRNClientResponseToApproval);
str.Append("</td>");
}
else
{
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
}
//29.1
str.Append("<td>");
str.Append(i.FromArrivalToDeposit);
str.Append("</td>");
//30
if (DateTime.Parse(i.GRNCreatedDate) != DateTime.Parse("1/1/0001"))
{
str.Append("<td>");
str.Append(i.ArrivalToGRNCreation);
str.Append("</td>");
}
else
{
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
}
if (DateTime.Parse(i.GRNCleintResponse) != DateTime.Parse("1/1/0001"))
{
//31
str.Append("<td>");
str.Append(i.ArrivalToClientAccptance);
str.Append("</td>");
}
else
{
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
}
if (DateTime.Parse("1/1/0001") != DateTime.Parse(i.ManagerAppp))
{
//32
str.Append("<td>");
str.Append(i.ArrivalToGRNApproval);
str.Append("</td>");
}
else
{
str.Append("<td>");
str.Append(" ");
str.Append("</td>");
}
}
str.Append("</tr>");
}
str.Append("</table>".ToString());
}
}
}
}
