public List <Aemp1_Aido1> Get_Aemp1WithAido1_List(Aemp_Aido request) { List <Aemp1_Aido1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("TMS")) { string strSql = ""; string strAemp1Where = "Where CONVERT(varchar(20),PickupDateTime ,112)=(select convert(varchar(10),getdate(),112)) and Driver1Code ='" + request.DriverCode + "'"; string strAido1Where = "Where CONVERT(varchar(20),DeliveryDate ,112)=(select convert(varchar(10),getdate(),112)) and DriverCode ='" + request.DriverCode + "'"; strSql = " select cast(TrxNo as varchar(20)) as 'Key','Aemp1' as TableName, 'Collect' as DCFlag ,'' as UpdatedFlag ,isnull((cast(pcs as nvarchar(20))+' ' +UomCode),'') as PcsUom ," + " PickupDateTime as TimeFrom , CollectFromName as DeliveryToName, CollectFromAddress1 as DeliveryToAddress1 , " + " CollectFromAddress2 as DeliveryToAddress2 ,CollectFromAddress3 as DeliveryToAddress3 ,CollectFromAddress4 as DeliveryToAddress4 , " + " GrossWeight as Weight,Volume ,isnull(DeliveryInstruction1,'') as DeliveryInstruction1, isnull(DeliveryInstruction2,'') as DeliveryInstruction2, " + " isnull(DeliveryInstruction3, '') as DeliveryInstruction3,Remark as Remark,AttachmentFlag as AttachmentFlag ,isnull(JobNo,'') as JobNo,Case StatusCode When 'POD' then 'POD' Else (Case (Select Top 1 StatusCode from jmjm3 Where JobNo = Aemp1.JobNo Order By LineItemNo DESC) When 'CANCEL' then 'CANCEL' else Aemp1.StatusCode END) END AS StatusCode,'' AS CancelDescription , " + " Driver1Code as DriverCode , CONVERT(varchar(20),PickupDateTime ,112) as FilterTime , " + " isnull((select top 1 case isnull(Rcbp1.Handphone1, '') when '' then isnull(Rcbp1.Telephone, '') else Rcbp1.Handphone1 end from rcbp1 where rcbp1.BusinessPartyCode = aemp1.CollectFromCode ), '') AS PhoneNumber" + " from Aemp1 " + strAemp1Where + "" + " UNION all " + " select DeliveryOrderNo as 'Key','Aido1' as TableName, 'Deliver' as DCFlag ,'' as UpdatedFlag ,isnull((cast(OriginalPcs as nvarchar(20)) + ' ' + OriginCode),'') as PcsUom , " + " DeliveryDate as TimeFrom , DeliveryToName as DeliveryToName, DeliveryToAddress1 as DeliveryToAddress1 , " + " DeliveryToAddress2 as DeliveryToAddress2 ,DeliveryToAddress3 as DeliveryToAddress3 ,DeliveryToAddress4 as DeliveryToAddress4 , " + " Weight as Weight,0.0 as Volume ,isnull(DeliveryInstruction1,'') as DeliveryInstruction1, isnull(DeliveryInstruction2,'') as DeliveryInstruction2, " + " isnull(DeliveryInstruction3,'') as DeliveryInstruction3,Remark as Remark,AttachmentFlag as AttachmentFlag,isnull(JobNo,'') as JobNo,Case StatusCode When 'POD' then 'POD' Else (Select Top 1 StatusCode from jmjm3 Where JobNo = Aido1.JobNo Order By LineItemNo DESC) END AS StatusCode,'' AS CancelDescription , " + " DriverCode as DriverCode , CONVERT(varchar(20),DeliveryDate ,112) as FilterTime , " + " isnull((select top 1 case isnull(Rcbp1.Handphone1, '') when '' then isnull(Rcbp1.Telephone, '') else Rcbp1.Handphone1 end from rcbp1 where rcbp1.BusinessPartyCode = aido1.DeliveryToCode ), '') AS PhoneNumber" + " from Aido1 " + strAido1Where + ""; Result = db.Select <Aemp1_Aido1>(strSql); } } catch { throw; } return(Result); }
public int confirm_Aemp1WithAido1(Aemp_Aido request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.TableName == "Aemp1") { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD',ActualPickUpDateTime=getdate(),SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + request.Key + "'"); } else if (request.TableName == "Sido1") { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + request.Key + "'"); } else { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD',ActualDeliveryDateTime=getdate(),SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " DeliveryOrderNo='" + request.Key + "'"); } } } catch { throw; } return(Result); }
public int confirm_Aemp1WithAido1(Aemp_Aido request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.TableName == "Aemp1") { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD'", " TrxNo='" + request.Key + "'"); } else { db.Update(request.TableName, " Remark = '" + request.Remark + "',StatusCode = 'POD'", " DeliveryOrderNo='" + request.Key + "'"); } } } catch { throw; } return(Result); }
public List <Aemp1_Aido1> Get_Aemp1WithAido1_List(Aemp_Aido request) { List <Aemp1_Aido1> Result = null; try { using (var db = DbConnectionFactory.OpenDbConnection("TMS")) { string strSql = ""; string strRefreshAemp1 = ""; string strRefreshSido1 = ""; string strRefreshAido1 = ""; if (request.TableName == "Aemp1") // Refresh for Table { strRefreshAemp1 = " And TrxNo ='" + request.Key + "'"; strRefreshSido1 = " And TrxNo =0 "; strRefreshAido1 = " And DeliveryOrderNo = 0 "; } else if (request.TableName == "Aido1") { strRefreshAido1 = " And DeliveryOrderNo ='" + request.Key + "'"; strRefreshAemp1 = " And TrxNo =0 "; strRefreshSido1 = " And TrxNo =0 "; } else if (request.TableName == "Sido1") { strRefreshSido1 = " And TrxNo ='" + request.Key + "'"; strRefreshAemp1 = " And TrxNo =0 "; strRefreshAido1 = " And DeliveryOrderNo =0 "; } string strAemp1Where = "Where CONVERT(varchar(20),PickupDateTime ,112)=(select convert(varchar(10),getdate(),112)) and Driver1Code ='" + request.DriverCode + "' " + strRefreshAemp1; string strAido1Where = "Where CONVERT(varchar(20),DeliveryDate ,112)=(select convert(varchar(10),getdate(),112)) and DriverCode ='" + request.DriverCode + "'" + strRefreshAido1; string strSido1Where = "Where CONVERT(varchar(20),CollectDateTime ,112)=(select convert(varchar(10),getdate(),112)) and DriverCode ='" + request.DriverCode + "'" + strRefreshSido1; strSql = " select cast(TrxNo as varchar(20)) as 'Key','Aemp1' as TableName, 'Collect' as DCFlag ,'' as UpdatedFlag ,isnull((cast(pcs as nvarchar(20))+' ' +ISNULL(UomCode,'')),'') as PcsUom ," + " PickupDateTime as TimeFrom , CollectFromName as DeliveryToName, CollectFromAddress1 as DeliveryToAddress1 , " + " CollectFromAddress2 as DeliveryToAddress2 ,CollectFromAddress3 as DeliveryToAddress3 ,CollectFromAddress4 as DeliveryToAddress4 , " + " GrossWeight as Weight,Volume ,(Select ISNULL( CommodityDescription,'') From Aiaw1 Where Aiaw1.JobNo=Aemp1.JobNo ) as DeliveryInstruction1, (Select ISNULL( CommodityDescription01,'') From Aiaw1 Where Aiaw1.JobNo=Aemp1.JobNo ) as DeliveryInstruction2, " + " '' as DeliveryInstruction3,Remark as Remark,AttachmentFlag as AttachmentFlag ,isnull(JobNo,'') as JobNo,Case StatusCode When 'POD' then 'POD' Else (Case (Select Top 1 StatusCode from jmjm3 Where JobNo = Aemp1.JobNo Order By LineItemNo DESC) When 'CANCEL' then 'CANCEL' else Aemp1.StatusCode END) END AS StatusCode,'' AS CancelDescription , " + " Driver1Code as DriverCode , CONVERT(varchar(20),PickupDateTime ,112) as FilterTime , " + " (Select ISNULL(consigneetelephone,'') From Aiaw1 Where Aiaw1.JobNo = Aemp1.JobNo) AS PhoneNumber," + " ISNULL(SignBy,'') AS SignBy " + " from Aemp1 " + strAemp1Where + "" + " UNION all " + " select DeliveryOrderNo as 'Key','Aido1' as TableName, 'Deliver' as DCFlag ,'' as UpdatedFlag ,isnull((cast(OriginalPcs as nvarchar(20)) + ' ' + ISNULL(OriginCode,'')),'') as PcsUom , " + " DeliveryDate as TimeFrom , DeliveryToName as DeliveryToName, DeliveryToAddress1 as DeliveryToAddress1 , " + " DeliveryToAddress2 as DeliveryToAddress2 ,DeliveryToAddress3 as DeliveryToAddress3 ,DeliveryToAddress4 as DeliveryToAddress4 , " + " Weight as Weight,0.0 as Volume ,(Select ISNULL( CommodityDescription,'') From Aiaw1 Where Aiaw1.JobNo=Aido1.JobNo ) as DeliveryInstruction1, (Select ISNULL( CommodityDescription01,'') From Aiaw1 Where Aiaw1.JobNo=Aido1.JobNo ) as DeliveryInstruction2, " + " '' as DeliveryInstruction3,Remark as Remark,AttachmentFlag as AttachmentFlag,isnull(JobNo,'') as JobNo,Case StatusCode When 'POD' then 'POD' Else (Select Top 1 StatusCode from jmjm3 Where JobNo = Aido1.JobNo Order By LineItemNo DESC) END AS StatusCode,'' AS CancelDescription , " + " DriverCode as DriverCode , CONVERT(varchar(20),DeliveryDate ,112) as FilterTime , " + " (Select ISNULL(consigneetelephone,'') From Aiaw1 Where Aiaw1.JobNo = Aido1.JobNo) AS PhoneNumber ," + " ISNULL(SignBy,'') AS SignBy " + " from Aido1 " + strAido1Where + " UNION all " + " select cast(TrxNo as varchar(20)) as 'Key','Sido1' as TableName, 'Collect' as DCFlag ,'' as UpdatedFlag ,isnull((cast(TotalPcs as nvarchar(20)) ), '') as PcsUom ," + " CollectDateTime as TimeFrom , CollectFromName as DeliveryToName, CollectFromAddress1 as DeliveryToAddress1 , " + " CollectFromAddress2 as DeliveryToAddress2 ,CollectFromAddress3 as DeliveryToAddress3 ,CollectFromAddress4 as DeliveryToAddress4 , " + " TotalGrossWeight as Weight,TotalVolume as Volume ,(Select ISNULL( CommodityDescription,'') From Sibl1 Where Sibl1.JobNo=Sido1.JobNo ) as DeliveryInstruction1, (Select ISNULL( CommodityDescription01,'') From Sibl1 Where Sibl1.JobNo=Sido1.JobNo ) as DeliveryInstruction2, " + " '' as DeliveryInstruction3,Remark as Remark,AttachmentFlag as AttachmentFlag ,isnull(JobNo,'') as JobNo,Case StatusCode When 'POD' then 'POD' Else (Case (Select Top 1 StatusCode from jmjm3 Where JobNo = Sido1.JobNo Order By LineItemNo DESC) When 'CANCEL' then 'CANCEL' else Sido1.StatusCode END) END AS StatusCode,'' AS CancelDescription , " + " DriverCode as DriverCode , CONVERT(varchar(20),CollectDateTime ,112) as FilterTime , " + " (Select ISNULL(consigneetelephone,'') From Sibl1 Where Sibl1.JobNo = Sido1.JobNo) AS PhoneNumber ," + " ISNULL(SignBy,'') AS SignBy " + " from Sido1 " + strSido1Where + ""; Result = db.Select <Aemp1_Aido1>(strSql); } } catch { throw; } return(Result); }
public int UpdateAll_Aemp1WithAido1(Aemp_Aido request) { int Result = -1; try { using (var db = DbConnectionFactory.OpenDbConnection()) { if (request.UpdateAllString != null && request.UpdateAllString != "") { JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString); if (ja != null) { for (int i = 0; i < ja.Count(); i++) { if (ja[i]["TableName"] == null || ja[i]["TableName"].ToString() == "") { continue; } string strKey = ja[i]["Key"].ToString(); string strTableName = ja[i]["TableName"].ToString(); string strRemark = ""; string strStatusCode = ""; if (ja[i]["Remark"] != null || ja[i]["Remark"].ToString() != "") { strRemark = ja[i]["Remark"].ToString(); } if (ja[i]["StatusCode"] != null || ja[i]["StatusCode"].ToString() != "") { strStatusCode = ja[i]["StatusCode"].ToString(); } if (strStatusCode.ToLower() == "cancel") { string strJobNo = ""; if (ja[i]["JobNo"] != null || ja[i]["JobNo"].ToString() != "") { strJobNo = ja[i]["JobNo"].ToString(); } if (strJobNo != "") { int intMaxLineItemNo = 1; List <Jmjm3> list1 = db.Select <Jmjm3>("Select Max(LineItemNo) LineItemNo from Jmjm3 Where JobNo = " + Modfunction.SQLSafeValue(strJobNo)); if (list1 != null) { if (list1[0].LineItemNo > 0) { intMaxLineItemNo = list1[0].LineItemNo + 1; } } db.Insert(new Jmjm3 { JobNo = strJobNo, DateTime = DateTime.Now, UpdateDatetime = DateTime.Now, LineItemNo = intMaxLineItemNo, AutoFlag = "N", StatusCode = "CANCEL", UpdateBy = ja[0]["DriverCode"] == null ? "" : Modfunction.SQLSafe(ja[0]["DriverCode"].ToString()), Remark = Modfunction.SQLSafe(strRemark), Description = ja[0]["CancelDescription"] == null ? "" : Modfunction.SQLSafe(ja[0]["CancelDescription"].ToString()) }); if (strTableName == "Aemp1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "'", " TrxNo='" + strKey + "'"); } else if (strTableName == "Sido1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "'", " TrxNo='" + strKey + "'"); } else { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "'", " DeliveryOrderNo='" + strKey + "'"); } } } else { if (strTableName == "Aemp1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + strKey + "'"); } else if (strTableName == "Sido1") { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " TrxNo='" + strKey + "'"); } else { db.Update(strTableName, " Remark = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "',SignBy='" + Modfunction.CheckNull(request.SignBy) + "'", " DeliveryOrderNo='" + strKey + "'"); } } } Result = 1; } } } } catch { throw; } return(Result); }