public List <Tobk1> Get_Tobk1_SpsList(Tobk request)
{
List <Tobk1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
int count = 0;
if (!string.IsNullOrEmpty(request.RecordCount))
{
count = int.Parse(request.RecordCount);
}
string strWhere = "";
if (!string.IsNullOrEmpty(request.BookingNo))
{
strWhere = "Where BookingNo='" + request.BookingNo + "'";
}
var strSQL = "SELECT t1.BookingNo,JobNo,CustomerCode,CustomerName,CustomerRefNo,DeliveryEndDateTime,TotalPcs,Toaddress1,Toaddress2,Toaddress3,Toaddress4,UOMCode" +
" FROM Tobk1 t1," +
"(SELECT TOP " + (count + 20) + "row_number() OVER(ORDER BY bookingNo ASC) n, bookingNo FROM Tobk1 " + strWhere + " ) t2 " +
"WHERE t1.bookingNo = t2.bookingNo AND StatusCode<> 'DEL' AND t2.n >" + count +
"ORDER BY t2.n ASC";
Result = db.Select <Tobk1>(strSQL);
}
}
catch { throw; }
return(Result);
}
public List <Tobk1> Get_Tobk1_List(Tobk request)
{
List <Tobk1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
string strSql = "";
strSql = " select BookingNo as 'Key','Tobk1' as TableName, Case JobType when 'CO' then 'Collect' when 'DE' then 'Deliver' when 'TP' then 'Transport' else '' end as DCFlag ,'' as UpdatedFlag ,isnull((cast(TotalPcs as nvarchar(20))+' ' +UomCode),'') as PcsUom ," +
"Case JobType when 'CO' then FromName else ToName END as DeliveryToName ,DeliveryEndDateTime as TimeFrom ," +
"Case JobType when 'CO' then FromAddress1 else ToAddress1 END as DeliveryToAddress1 , " +
" Case JobType when 'CO' then FromAddress2 else ToAddress2 END as DeliveryToAddress2 ,Case JobType when 'CO' then FromAddress3 else ToAddress3 END as DeliveryToAddress3 ,Case JobType when 'CO' then FromAddress4 else ToAddress4 END as DeliveryToAddress4 , " +
"TotalGrossWeight as Weight,TotalVolume As Volume ,isnull(Description, '') as DeliveryInstruction1, '' as DeliveryInstruction2, " +
"'' as DeliveryInstruction3,DescriptionOfGoods1 AS CargoDescription,Note 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 = Tobk1.JobNo Order By LineItemNo DESC) When 'CANCEL' then 'CANCEL' else Tobk1.StatusCode END) END AS StatusCode,'' AS CancelDescription," +
"DriverCode, CONVERT(varchar(20), DeliveryEndDateTime, 112) as FilterTime , " +
"Case JobType when 'CO' then 'PC ' + isnull(FromPostalCode, '') else 'PC ' + isnull(ToPostalCode, '') END as PostalCode,NoOfPallet,OnBehalfName,TotalPcs," +
" isnull((Case JobType when 'CO' then(select top 1 case isnull(Rcbp1.Handphone1, '') when '' then isnull(Rcbp1.Telephone, '') else Rcbp1.Handphone1 end from rcbp1 where rcbp1.BusinessPartyCode = Tobk1.FromCode ) else (select top 1 case isnull(Rcbp1.Handphone1, '') when '' then isnull(Rcbp1.Telephone, '') else Rcbp1.Handphone1 end from rcbp1 where rcbp1.BusinessPartyCode = Tobk1.ToCode) End), '') AS PhoneNumber" +
" from Tobk1 Where CONVERT(varchar(20),DeliveryEndDateTime ,112)=(select convert(varchar(10),getdate(),112)) and DriverCode ='" + request.DriverCode + "'";
Result = db.Select <Tobk1>(strSql);
}
}
catch { throw; }
return(Result);
}
public int update_tobk1(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
if (!string.IsNullOrEmpty(request.CompletedFlag))
{
Result = db.Update <Tobk1>(
new
{
CompletedFlag = request.CompletedFlag
},
p => p.BookingNo == request.BookingNo
);
}
else if (!string.IsNullOrEmpty(request.Remark))
{
Result = db.Update <Tobk1>(
new
{
Note = request.Remark
},
p => p.BookingNo == request.BookingNo
);
}
}
}
catch { throw; }
return(Result);
}
public List <Tobk1> Get_Tobk1_List(Tobk request)
{
List <Tobk1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
int count = 0;
if (!string.IsNullOrEmpty(request.RecordCount))
{
count = int.Parse(request.RecordCount);
}
string strWhere = "";
if (!string.IsNullOrEmpty(request.BookingNo))
{
strWhere = "Where BookingNo='" + request.BookingNo + "'";
// var strSQL = "SELECT t1.BookingNo,JobNo,CustomerCode,CustomerName,CustomerRefNo,CompletedFlag,DeliveryEndDateTime,TotalPcs,Toaddress1,Toaddress2,Toaddress3,Toaddress4,UOMCode" _
//var strSQL = " SELECT t1.BookingNo,JobNo,isnull(CustomerCode,'' ) AS CustomerCode,isnull(CustomerName,'') as CustomerName," + //20160629
// "isnull(CustomerRefNo,'') as CustomerRefNo,isnull(CompletedFlag,'') AS CompletedFlag,DeliveryEndDateTime,TotalPcs," +
// "isnull(JobType,'')as JobType,EstimateDeliveryDateTime,isnull(FromPostalCode,'')as FromPostalCode,isnull(FromName,'') as FromName, isnull(FromAddress1,'')as FromAddress1 ,isnull(FromAddress2,'')as FromAddress2,isnull(FromAddress3,'')as FromAddress3,isnull(FromAddress4,'')as FromAddress4," +
// "isnull(ToPostalCode,'') as ToPostalCode,isnull(ToName,'')as ToName,isnull(ToAddress1,'') as ToAddress1,isnull(ToAddress2,'') as ToAddress2," +
// "isnull(ToAddress3,'') as ToAddress3,isnull(ToAddress4,'') as ToAddress4,isnull(UomCode,'')as UomCode" +
// " FROM Tobk1 t1," +
// "(SELECT TOP " + (count + 20) + "row_number() OVER(ORDER BY bookingNo ASC) n, bookingNo FROM Tobk1 " + strWhere + " ) t2 " +
// "WHERE t1.bookingNo = t2.bookingNo AND StatusCode<> 'DEL' AND t2.n >" + count +
// "ORDER BY t2.n ASC";
var strSQL = "SELECT BookingNo, JobNo, isnull(CustomerCode,'' ) AS CustomerCode, isnull(CustomerName,'') as CustomerName," +
" isnull(CustomerRefNo, '') as CustomerRefNo,isnull(CompletedFlag, '') AS CompletedFlag, DeliveryEndDateTime, TotalPcs," +
" isnull(JobType,'')as JobType,EstimateDeliveryDateTime,isnull(FromPostalCode, '') as FromPostalCode,isnull(FromName, '') as FromName, isnull(FromAddress1, '') as FromAddress1 ,isnull(FromAddress2, '') as FromAddress2,isnull(FromAddress3, '') as FromAddress3,isnull(FromAddress4, '') as FromAddress4," +
" isnull(ToPostalCode, '') as ToPostalCode,isnull(ToName, '') as ToName,isnull(ToAddress1, '') as ToAddress1,isnull(ToAddress2, '') as ToAddress2," +
" isnull(ToAddress3, '') as ToAddress3,isnull(ToAddress4, '') as ToAddress4,isnull(UomCode, '') as UomCode" +
", Tobk1.TotalGrossWeight,Tobk1.NoOfPallet,Tobk1.TotalVolume,isnull(Tobk1.DescriptionOfGoods1,'')as DescriptionOfGoods1 ,isnull(Tobk1.Description,'') as Description,isnull(Tobk1.Note,'') as Note" +
" FROM Tobk1 where BookingNo='" + request.BookingNo + "'";
Result = db.Select <Tobk1>(strSQL);
}
else if (!string.IsNullOrEmpty(request.DriverCode))
{
var strSQL = "SELECT BookingNo, JobNo, isnull(CustomerCode,'' ) AS CustomerCode, isnull(CustomerName,'') as CustomerName," +
" isnull(CustomerRefNo, '') as CustomerRefNo,isnull(CompletedFlag, '') AS CompletedFlag, DeliveryEndDateTime, TotalPcs," +
" isnull(JobType,'')as JobType,EstimateDeliveryDateTime,isnull(FromPostalCode, '') as FromPostalCode,isnull(FromName, '') as FromName, isnull(FromAddress1, '') as FromAddress1 ,isnull(FromAddress2, '') as FromAddress2,isnull(FromAddress3, '') as FromAddress3,isnull(FromAddress4, '') as FromAddress4," +
" isnull(ToPostalCode, '') as ToPostalCode,isnull(ToName, '') as ToName,isnull(ToAddress1, '') as ToAddress1,isnull(ToAddress2, '') as ToAddress2," +
" isnull(ToAddress3, '') as ToAddress3,isnull(ToAddress4, '') as ToAddress4,isnull(UomCode, '') as UomCode" +
", Tobk1.TotalGrossWeight,Tobk1.NoOfPallet,Tobk1.TotalVolume,isnull(Tobk1.DescriptionOfGoods1,'')as DescriptionOfGoods1 ,isnull(Tobk1.Description,'') as Description,isnull(Tobk1.Note,'') as Note" +
" FROM Tobk1 where DriverCode='" + request.DriverCode + "'";
Result = db.Select <Tobk1>(strSQL);
}
}
//using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
//{
// string strSQL = "select BookingNo,JobNo,CustomerCode,CustomerName,CustomerRefNo,DeliveryEndDateTime,TotalPcs,Toaddress1,Toaddress2,Toaddress3,Toaddress4,UOMCode from tobk1 where StatusCode <>'DEL'";
// Result = db.Select<Tobk1>(strSQL);
//}
}
catch { throw; }
return(Result);
}
public List <tjms2> Get_tjms2_List(Tobk request)
{
List <tjms2> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
string strSql = "";
strSql = " select isnull((select tjms1.JobNo from tjms1 where trxno = tjms2.TrxNo),'') as JobNo ," +
" tjms2.TrxNo, " +
" tjms2.LineItemNo," +
" TJMS2.Pcs," +
" TJMS2.GrossWeight," +
" isnull(TJMS2.CargoDescription1, '') as CargoDescription1," +
" isnull(TJMS2.CargoDescription2, '') as CargoDescription2," +
" isnull(TJMS2.CargoDescription3, '') as CargoDescription3," +
" isnull(TJMS2.OfficeInChargeName, '') as OfficeInChargeName," +
" isnull((select tjms3.BargeName from tjms3 where trxno = tjms2.TrxNo and LineItemNo = 1) ,'') as BargeName1 ," +
" isnull((select tjms3.BargeName from tjms3 where trxno = tjms2.TrxNo and LineItemNo = 2) ,'') as BargeName2 ," +
" isnull((select tjms3.BargeName from tjms3 where trxno = tjms2.TrxNo and LineItemNo = 3) ,'') as BargeName3 ," +
" (select tjms1.DateCompleted from tjms1 where trxno = tjms2.TrxNo) as DateCompleted," +
" isnull(TJMS2.ContainerNo1, '') as ContainerNo1," +
" isnull(TJMS2.ContainerType1, '') as ContainerType1," +
" tjms2.Pcs1 as Pcs1," +
" isnull(TJMS2.CargoDescription1, '') as CargoDescription1, " +
" isnull(TJMS2.VehicleNo1, '') as VehicleNo1," +
" (select tjms4.StartDateTime from tjms4 where tjms4.trxno = tjms2.TrxNo and tjms4.LineItemNo = tjms2.lineItemNo) as StartDateTime," +
" (select tjms4.EndDateTime from tjms4 where tjms4.trxno = tjms2.TrxNo and tjms4.LineItemNo = tjms2.lineItemNo) as EndDateTime," +
" isnull(TJMS2.ContainerNo2, '') as ContainerNo2," +
" isnull(TJMS2.ContainerType2, '') as ContainerType2," +
" tjms2.Pcs2 as Pcs2," +
" isnull(TJMS2.CargoDescription2, '') as CargoDescription2," +
" isnull(TJMS2.VehicleNo2, '') as VehicleNo2," +
" isnull(TJMS2.ContainerNo3, '') as ContainerNo3," +
" isnull(TJMS2.ContainerType3, '') as ContainerType3," +
" tjms2.Pcs3 as Pcs3," +
" isnull(TJMS2.CargoDescription3, '') as CargoDescription3," +
" isnull(TJMS2.VehicleNo3, '') as VehicleNo3," +
" (select tjms1.ChargeBerthQty from tjms1 where trxno = tjms2.TrxNo) as ChargeBerthQty," +
" (select tjms1.ChargeLiftingQty from tjms1 where trxno = tjms2.TrxNo) as ChargeLiftingQty," +
" (select tjms1.SignalManQty from tjms1 where trxno = tjms2.TrxNo) as SignalManQty," +
" isnull((select tjms1.ChargeOther from tjms1 where trxno = tjms2.TrxNo),'') as ChargeOther," +
" isnull((select tjms1.SignedByName from tjms1 where trxno = tjms2.TrxNo),'') as SignedByName," +
" isnull((select tjms1.SignedByNric from tjms1 where trxno = tjms2.TrxNo),'') as SignedByNric," +
" isnull((select tjms1.SignedByDesignation from tjms1 where trxno = tjms2.TrxNo),'') as SignedByDesignation," +
" isnull((select tjms1.RecStatus from tjms1 where trxno = tjms2.TrxNo),'') as StatusCode," +
" (select tjms1.CustomerSignOffDateTime from tjms1 where trxno = tjms2.TrxNo) as CustomerSignOffDateTime, " +
" isnull((select CompanyName from saco1),'') as CompanyName" +
" from tjms2 where trxno = " + request.TrxNo + " ";
Result = db.Select <tjms2>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Tobk2> Get_Tobk2_List(Tobk request)
{
List <Tobk2> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
if (!string.IsNullOrEmpty(request.BookingNo))
{
var strSQL = "select Tobk1.JobType,Tobk1.CustomerRefNo, Tobk1.TotalGrossWeight,Tobk1.TotalVolume, Tobk1.TotalChargeWeight, Tobk2.ContainerType ,Tobk2.DeliveryPcs,Tobk2.Pcs from Tobk2 left join Tobk1 on tobk2.bookingNo = Tobk1.BookingNo where tobk1.BookingNo=" + request.BookingNo;
Result = db.Select <Tobk2>(strSQL);
}
}
}
catch { throw; }
return(Result);
}
public int confirm_Tobk1(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
string str;
str = " Note = " + Modfunction.SQLSafeValue(request.Remark) + ",OnBehalfName = " + Modfunction.SQLSafeValue(request.OnBehalfName) + ",StatusCode = 'POD'";
db.Update(request.TableName,
str,
" BookingNo='" + request.Key + "'");
}
}
catch { throw; }
return(Result);
}
public int confirm_tobk1(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection())
{
Result = db.Update <Tobk1>(
new
{
CompletedFlag = "Y"
},
p => p.BookingNo == request.BookingNo
);
}
}
catch { throw; }
return(Result);
}
public List <Tjms5> Get_tjms5_List(Tobk request)
{
List <Tjms5> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
string strSql = "";
string strWhere = "";
if (request.LineItemNo != null && request.LineItemNo.ToString() != "")
{
strWhere = " where trxno = " + request.TrxNo + " And LineItemNo=" + request.LineItemNo + "";
}
else
{
strWhere = " where trxno = " + request.TrxNo + "";
}
strSql =
" select ISNULL( EquipmentType,'') as EquipmentType , " +
" LineItemNo ," +
" TrxNo , " +
" isnull((select tjms1.RecStatus from tjms1 where tjms1.trxno = tjms5.TrxNo),'') as StatusCode," +
" ISNULL( EquipmentTypeDescription,'') as EquipmentTypeDescription," +
" ISNULL(ContainerNo,'') as ContainerNo ," +
" ISNULL(CargoDescription ,'') as CargoDescription," +
" Volume ," +
" ChargeWeight ," +
" ChgWtRoundUp," +
" ISNULL(VehicleNo ,'') as VehicleNo," +
" '' as disabled ," +
" (select Top 1 StartDateTime from tjms4 where tjms4.TrxNo = tjms5.TrxNo ) as StartDateTime," +
" (select Top 1 EndDateTime from tjms4 where tjms4.TrxNo = tjms5.TrxNo ) as EndDateTime " +
" from tjms5 " + strWhere;
Result = db.Select <Tjms5>(strSql);
}
}
catch { throw; }
return(Result);
}
public List <Tovt1> list_Tovt1(Tobk request)
{
List <Tovt1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
if (!string.IsNullOrEmpty(request.EquipmentType))
{
if (!string.IsNullOrEmpty(request.EquipmentType))
{
string strSQL = "Select ISNULL(VehicleType,'') as EquipmentType, ISNULL(VehicleTypeDescription,'') as EquipmentTypeDescription,Volume,ChgWt,ISNULL(EditFlag,'N') as EditFlag From Tovt1 Where VehicleTypeDescription = '" + request.EquipmentType + "'";
Result = db.Select <Tovt1>(strSQL);
}
}
}
}
catch { throw; }
return(Result);
}
public int DeleteLineItem(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
if (request.TrxNo != null && request.TrxNo != "" && request.LineItemNo != null && request.LineItemNo != "")
{
string strSql = "";
strSql = "Delete from Tjms5 where " +
"TrxNo ='" + request.TrxNo + "'" +
"And LineItemNo=" + request.LineItemNo + "";
db.ExecuteSql(strSql);
}
}
Result = 1;
}
catch { throw; }
return(Result);
}
public List <Tovt1> Get_EquipmentType(Tobk request)
{
List <Tovt1> Result = null;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
if (!string.IsNullOrEmpty(request.EquipmentType))
{
if (!string.IsNullOrEmpty(request.EquipmentType))
{
//string strSQL = "select DISTINCT CustomerCode as BusinessPartyCode ,CustomerName as BusinessPartyName from tjms1 where IsNUll(StatusCode,'')<>'DEL' And CustomerCode LIKE '" + request.BusinessPartyName + "%' Order By tjms1.CustomerCode Asc";
string strSQL = "Select VehicleTypeDescription as EquipmentType From Tovt1 Where VehicleTypeDescription LIKE '" + request.EquipmentType + "%'";
Result = db.Select <Tovt1>(strSQL);
}
}
}
}
catch { throw; }
return(Result);
}
public int UpdateAll_tjms2(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
//if (request.UpdateAllString != null && request.UpdateAllString != "")
//{
// JArray ja = (JArray)JsonConvert.DeserializeObject(request.UpdateAllString);
// if (ja != null)
// {
// for (int i = 0; i < ja.Count(); i++)
// {
// int ChargeBerthQty;
// int ChargeLiftingQty;
// if (ja[i]["TrxNo"] == null || ja[i]["TrxNo"].ToString() == "")
// { continue; }
// string strTrxNo = ja[i]["TrxNo"].ToString();
// string strLineItemNo = ja[i]["LineItemNo"].ToString();
// string SignedByName = ja[i]["SignedByName"].ToString();
// string SignedByNric = ja[i]["SignedByNric"].ToString();
// string SignedByDesignation = ja[i]["SignedByDesignation"].ToString();
// string CompanyName = ja[i]["CompanyName"].ToString();
// string strDateCompleted = ja[i]["DateCompleted"].ToString();
// string OfficeInChargeName = ja[i]["OfficeInChargeName"].ToString();
// if (request .ChargeBerthQty.ToString() == "")
// {
//request.ChargeBerthQty = 0;
// }
// else
// {
//request.ChargeBerthQty = int.Parse(ja[i]["ChargeBerthQty"].ToString());
// }
// if (ja[i]["ChargeLiftingQty"].ToString() == "")
// {
// ChargeLiftingQty = 0;
// }
// else
// {
// ChargeLiftingQty = int.Parse(ja[i]["ChargeLiftingQty"].ToString());
// }
// string ChargeOther = ja[i]["ChargeOther"].ToString();
DateTime dt = DateTime.Now;
//if (strDateCompleted != "" && strDateCompleted != null) {
// strDateCompleted = strDateCompleted +" "+ dt.GetDateTimeFormats('t')[0].ToString();
//}
string str;
if (request.LineItemNo != "0")
{
str = "AttachmentFlag='Y' , CustomerSignOffDateTime=GetDate(), completiondate=GetDate(), DateCompleted=GetDate() , SignedByName = " + Modfunction.SQLSafeValue(request.SignedByName) + ",SignedByNric= " + Modfunction.SQLSafeValue(request.SignedByNric) + ",SignedByDesignation= " + Modfunction.SQLSafeValue(request.SignedByDesignation) + ",ChargeBerthQty=" + request.ChargeBerthQty + ",ChargeLiftingQty=" + request.ChargeLiftingQty + ",SignalManQty=" + request.SignalManQty + ", ChargeOther=" + Modfunction.SQLSafeValue(request.ChargeOther) + "";
db.Update("tjms1",
str,
" TrxNo='" + request.TrxNo + "' ");
str = "OfficeInChargeName= " + Modfunction.SQLSafeValue(request.OfficeInChargeName) + "";
db.Update("tjms2",
str,
" TrxNo='" + request.TrxNo + "' ");
str = " CompanyName = " + Modfunction.SQLSafeValue(request.CompanyName) + "";
db.Update("saco1",
str
);
}
Result = 1;
}
}
catch { throw; }
return(Result);
}
public int confirm_tjms2(Tobk request)
{
int Result = -1;
return(Result);
}
public int UpdateAll_Tobk1(Tobk 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 = "";
string strOnBehalfName = "";
if (ja[i]["OnBehalfName"] != null || ja[i]["OnBehalfName"].ToString() != "")
{
strOnBehalfName = ja[i]["OnBehalfName"].ToString();
}
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())
});
db.Update(strTableName,
" Note = '" + Modfunction.SQLSafe(strRemark) + "', OnBehalfName = '" + Modfunction.SQLSafe(strOnBehalfName) + "'",
" BookingNo='" + strKey + "'");
}
}
else
{
db.Update(strTableName,
" Note = '" + Modfunction.SQLSafe(strRemark) + "',StatusCode = '" + strStatusCode + "', OnBehalfName = '" + Modfunction.SQLSafe(strOnBehalfName) + "'",
" BookingNo='" + strKey + "'");
}
}
Result = 1;
}
}
}
}
catch { throw; }
return(Result);
}
public int updatePickupTime(Tobk request)
{
int Result = -1;
return(Result);
}
public int UpdateAll_tjms5(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
//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 (request.TrxNo.Length > 0)
{
int TrxNo = Modfunction.ReturnZero(request.TrxNo.ToString());
string EquipmentType = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.EquipmentType));
string EquipmentTypeDescription = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.EquipmentTypeDescription));
string ContainerNo = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.ContainerNo));
string CargoDescription = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.CargoDescription));
double Volume = Modfunction.ReturnDobule(request.Volume);
double ChargeWeight = Modfunction.ReturnDobule(request.ChargeWeight);
double ChgWtRoundUp = Modfunction.ReturnDobule(request.ChgWtRoundUp);
string VehicleNo = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.VehicleNo));
int LineItemNo = Modfunction.ReturnZero(request.LineItemNo.ToString());
//int TrxNo = Modfunction.ReturnZero(request.TrxNo.ToString());
//string EquipmentType = Modfunction.SQLSafeValue(request.EquipmentType);
//string EquipmentTypeDescription = Modfunction.SQLSafeValue(request.EquipmentTypeDescription);
//string ContainerNo = Modfunction.SQLSafeValue(request.ContainerNo);
//string CargoDescription = Modfunction.SQLSafeValue(request.CargoDescription);
//int Volume = Modfunction.ReturnZero(request.Volume.ToString());
//int ChargeWeight = Modfunction.ReturnZero(request.ChargeWeight.ToString());
//int ChgWtRoundUp = Modfunction.ReturnZero(Modfunction.CheckNull(request.ChgWtRoundUp));
//string VehicleNo = Modfunction.SQLSafeValue(request.VehicleNo);
//int LineItemNo = Modfunction.ReturnZero(request.LineItemNo.ToString());
string strSql = "";
if (LineItemNo != 0)
{
strSql = "Update tjms5 set " +
"EquipmentType=" + EquipmentType + " , " +
"EquipmentTypeDescription=" + EquipmentTypeDescription + " ," +
"ContainerNo=" + ContainerNo + " , " +
"CargoDescription=" + CargoDescription + " , " +
"Volume=" + Volume + " ," +
"ChargeWeight=" + ChargeWeight + " , " +
"ChgWtRoundUp=" + ChgWtRoundUp + ", " +
"EditFlag=(select EditFlag from tovt1 where VehicleTypeDescription=" + EquipmentTypeDescription + " )," +
" VehicleNo=" + VehicleNo + " " +
"Where LineItemNo =" + LineItemNo + " And TrxNo=" + TrxNo + "";
db.ExecuteSql(strSql);
strSql = " update tjms2 set ChargeWeight = (select sum(ChgWtRoundUp) from tjms5 where TrxNo=" + TrxNo + ") Where LineItemNo =1 and TrxNo=" + TrxNo + " ";
db.ExecuteSql(strSql);
}
}
// }
//}
Result = 1;
//}
}
}
catch { throw; }
return(Result);
}
public int insert_tjms5(Tobk request)
{
int Result = -1;
try
{
using (var db = DbConnectionFactory.OpenDbConnection("TMS"))
{
//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 (request.TrxNo.Length > 0)
{
int TrxNo = Modfunction.ReturnZero(request.TrxNo.ToString());
string EquipmentType = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.EquipmentType));
string EquipmentTypeDescription = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.EquipmentTypeDescription));
string ContainerNo = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.ContainerNo));
string CargoDescription = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.CargoDescription));
double Volume = Modfunction.ReturnDobule(request.Volume);
double ChargeWeight = Modfunction.ReturnDobule(request.ChargeWeight);
double ChgWtRoundUp = Modfunction.ReturnDobule(request.ChgWtRoundUp);
string VehicleNo = Modfunction.SQLSafeValue(Modfunction.CheckNull(request.VehicleNo));
string startDateTime = "(select Top 1 ISNULL(StartDateTime,NULL) AS StartDateTime from tjms4 where TrxNo=" + TrxNo + ") ";
string endDateTime = "(select Top 1 ISNULL(EndDateTime,NULL) AS EndDateTime from tjms4 where TrxNo=" + TrxNo + ")";
string strSql = "";
int intMaxLineItemNo = 1;
List <Tjms5> list1 = db.Select <Tjms5>("Select Max(LineItemNo) LineItemNo from Tjms5 Where TrxNo = " + TrxNo);
if (list1 != null)
{
if (list1[0].LineItemNo > 0)
{
intMaxLineItemNo = list1[0].LineItemNo + 1;
}
}
if (intMaxLineItemNo != 0)
{
strSql = "insert into tjms5 (" +
" TrxNo ," +
" LineItemNo ," +
" EquipmentType ," +
" EquipmentTypeDescription ," +
" ContainerNo ," +
" CargoDescription ," +
" Volume ," +
" ChargeWeight ," +
" ChgWtRoundUp ," +
" editflag ," +
" VehicleNo " +
" )" +
"values( " +
TrxNo + " , " +
intMaxLineItemNo + " , " +
EquipmentType + " , " +
EquipmentTypeDescription + " , " +
ContainerNo + " , " +
CargoDescription + " , " +
Volume + " , " +
ChargeWeight + " , " +
ChgWtRoundUp + " , " +
"(Select isnull(editFlag,'') From tovt1 where VehicleTypeDescription =" + EquipmentTypeDescription + ")," +
VehicleNo + " " +
") ";
db.ExecuteSql(strSql);
strSql = " update tjms2 set ChargeWeight = (select sum(ChgWtRoundUp) from tjms5 where TrxNo=" + TrxNo + ") Where LineItemNo =1 and TrxNo=" + TrxNo + " ";
db.ExecuteSql(strSql);
}
}
// }
//}
Result = 1;
//}
}
}
catch { throw; }
return(Result);
}