private void Update_Det(XAApPayment inv, int index, decimal price, string code, string cntNo, string jobNo, string tripId)
{
try
{
string sql = string.Format(@"select SequenceId from XAApPaymentDet where Remark1='{0}'", code);
int id = SafeValue.SafeInt(Helper.Sql.One(sql), 0);
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.XAApPaymentDet), "SequenceId=" + id + "");
C2.XAApPaymentDet det = C2.Manager.ORManager.GetObject(query) as C2.XAApPaymentDet;
if (det != null)
{
det.DocDate = new DateTime(1900, 1, 1);
det.PayId = inv.SequenceId;
det.PayNo = inv.DocNo;
det.DocType = inv.DocType;
det.PayLineNo = index;
det.AcCode = System.Configuration.ConfigurationManager.AppSettings["DefaultCash"];
det.AcSource = "DB";
det.Remark1 = code;
det.Remark2 = "";
det.Remark3 = cntNo;
det.MastRefNo = jobNo;
det.JobRefNo = tripId;
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.ExRate = 1;
det.DocAmt += price;
if (det.ExRate == 0)
{
det.ExRate = 1;
}
det.LocAmt = price;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(det);
}
}
catch
{
}
}
private void InsertPayment()
{
for (int a = 0; a < list.Count; a++)
{
int trpId = list[a].id;
#region Select Trip
string sql = string.Format(@"with pri as (
select * from ctm_MastData where [Type]='tripcode'
),
tb1 as (
select det2.Id,det2.JobNo,det2.ContainerNo,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='DP' and ChgCode='Trip') as Incentive1,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='DP' and ChgCode='OverTime') as Incentive2,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='DP' and ChgCode='Standby') as Incentive3,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='DP' and ChgCode='PSA') as Incentive4,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='DHC') as Charge1,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='WEIGHING') as Charge2,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='WASHING') as Charge3,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='REPAIR') as Charge4,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='DETENTION') as Charge5,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='DEMURRAGE') as Charge6,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='LIFT_ON_OFF') as Charge7,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='C_SHIPMENT') as Charge8,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='EMF') as Charge9,
(select isnull(sum(isnull(Qty*Price,0)),0) from job_cost where TripNo=det2.Id and LineType='CL' and ChgCode='OTHER') as Charge10,
det1.SealNo,det1.ContainerType,det1.ScheduleDate,det2.FromDate,det2.FromTime,det2.ToDate,det2.ToTime,det2.TripCode,det2.TowheadCode,det2.Overtime,det2.OverDistance,
det2.ChessisCode,det2.FromCode,det2.ToCode,job.JobType,det2.ParkingZone,det2.DriverCode,
isnull((select top 1 case dri.ServiceLevel when 'Level1' then pri.Price1 when 'Level2' then pri.Price2 when 'Level3' then pri.Price3 else 0 end from pri where pri.Code=det2.TripCode),0) as TripCodePrice,
isnull((select top 1 case dri.ServiceLevel when 'Level1' then pri.Price1 when 'Level2' then pri.Price2 when 'Level3' then pri.Price3 else 0 end from pri where pri.Code=det2.Overtime),0) as OverTimePrice,
case when det2.OverDistance='Y' then isnull((select top 1 case dri.ServiceLevel when 'Level1' then pri.Price1 when 'Level2' then pri.Price2 when 'Level3' then pri.Price3 else 0 end from pri where pri.Code='OJ'),0) else 0 end as QJPrice
from CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
left outer join CTM_Job as job on job.jobNo=det2.JobNo
left outer join CTM_Driver as dri on dri.Code=det2.DriverCode ");
if (search_DateFrom.Date < new DateTime(1900, 1, 1))
{
search_DateFrom.Date = DateTime.Now.AddDays(-15);
}
if (search_DateTo.Date < new DateTime(1900, 1, 1))
{
search_DateTo.Date = DateTime.Now.AddDays(8);
}
string sql_part1 = string.Format(@")
select *,TripCodePrice+OverTimePrice+QJPrice as Total,isnull(Charge1,0)+isnull(Charge2,0)+isnull(Charge3,0)+isnull(Charge4,0)+isnull(Charge5,0)+isnull(Charge6,0)+isnull(Charge7,0)+isnull(Charge8,0)+isnull(Charge9,0)+isnull(Charge10,0) as TotalCharge from tb1");
string where = string.Format(@" where det2.Id={2} and det2.Statuscode='C' and DATEDIFF(d,det2.ToDate,'{0}')<=0 and DATEDIFF(d,det2.ToDate,'{1}')>=0", search_DateFrom.Date, search_DateTo.Date, trpId);
if (search_Driver.Text.Trim().Length > 0)
{
where += string.Format(@" and det2.DriverCode='{0}'", search_Driver.Text);
}
if (search_TowheadCode.Text.Trim().Length > 0)
{
where += string.Format(@" and det2.TowheadCode='{0}'", search_TowheadCode.Text);
}
if (cbb_Trip_TripCode.Text.Trim() != "")
{
where += string.Format(@" and det2.TripCode='{0}'", cbb_Trip_TripCode.Text);
}
if (cbb_zone.Text.Trim() != "")
{
where += string.Format(@" and det2.ParkingZone='{0}'", cbb_zone.Text);
}
#endregion
string new_sql = sql + where + sql_part1;
DataTable tab = ConnectSql.GetTab(new_sql);
int id = 0;
string str = "";
for (int i = 0; i < tab.Rows.Count; i++)
{
#region
string tripId = SafeValue.SafeString(tab.Rows[i]["Id"]);
DateTime toDate = SafeValue.SafeDate(tab.Rows[i]["ToDate"], DateTime.Today);
string contNo = SafeValue.SafeString(tab.Rows[i]["ContainerNo"]);
string cntType = SafeValue.SafeString(tab.Rows[i]["ContainerType"]);
string primeMover = SafeValue.SafeString(tab.Rows[i]["TowheadCode"]);
string trailer = SafeValue.SafeString(tab.Rows[i]["ChessisCode"]);
string driver = SafeValue.SafeString(tab.Rows[i]["DriverCode"]);
string jobNo = SafeValue.SafeString(tab.Rows[i]["JobNo"]);
decimal dhc = SafeValue.SafeDecimal(tab.Rows[i]["Charge1"], 0);
decimal weight = SafeValue.SafeDecimal(tab.Rows[i]["Charge2"], 0);
decimal washing = SafeValue.SafeDecimal(tab.Rows[i]["Charge3"], 0);
decimal repair = SafeValue.SafeDecimal(tab.Rows[i]["Charge4"], 0);
decimal detention = SafeValue.SafeDecimal(tab.Rows[i]["Charge5"], 0);
decimal demurrage = SafeValue.SafeDecimal(tab.Rows[i]["Charge6"], 0);
decimal lift = SafeValue.SafeDecimal(tab.Rows[i]["Charge7"], 0);
decimal c_Shipment = SafeValue.SafeDecimal(tab.Rows[i]["Charge8"], 0);
decimal emf = SafeValue.SafeDecimal(tab.Rows[i]["Charge10"], 0);
decimal other = SafeValue.SafeDecimal(tab.Rows[i]["Charge9"], 0);
DateTime voucher_date = date_voucher.Date;
if (dhc > 0 || weight > 0 || washing > 0 || repair > 0 || detention > 0 || demurrage > 0 || lift > 0 || c_Shipment > 0 || emf > 0 || other > 0)
{
sql = string.Format(@"select count(*) from XAApPayment where PartyTo='CASH' and OtherPartyName='{0}' and convert(nvarchar(100),DocDate,23)='{1}'", driver, voucher_date.ToString("yyyy-MM-dd"));
int cnt = SafeValue.SafeInt(Helper.Sql.One(sql), 0);
if (cmb_voucher_mode.Value.ToString() == "New" && a == 0)
{
cnt = 0;
}
XAApPayment inv = null;
if (cnt == 0)
{
#region Payment
inv = new C2.XAApPayment();
inv.PartyTo = "CASH";
inv.OtherPartyName = SafeValue.SafeString(driver);
string invN = C2Setup.GetNextNo("PS", "AP-PAYMENT-Cash", voucher_date);
inv.DocNo = invN;
inv.DocType = "PS";
inv.DocType1 = "Cash";
inv.DocDate = voucher_date;
string[] currentPeriod = EzshipHelper.GetAccPeriod(voucher_date);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], voucher_date.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], voucher_date.Month);
inv.ChqDate = voucher_date;
inv.Remark = "";
inv.CurrencyId = "SGD";
inv.ExRate = 1;
inv.PoNo = "";
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = System.Configuration.ConfigurationManager.AppSettings["DefaultBankCode"];
inv.AcSource = "CR";
if (inv.DocType1.ToLower() == "refund")
{
inv.AcSource = "DB";
}
//inv.BankName = bankName.Text;
inv.ChqNo = "";
inv.ExportInd = "N";
inv.DocAmt = 0;
inv.LocAmt = inv.DocAmt * inv.ExRate;
inv.Pic = "";
inv.BankRec = "";
inv.BankDate = new DateTime(1900, 1, 1);
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
inv.CreateBy = HttpContext.Current.User.Identity.Name;
inv.CreateDateTime = DateTime.Now;
inv.UpdateBy = HttpContext.Current.User.Identity.Name;
inv.UpdateDateTime = DateTime.Now;
inv.PostBy = "";
inv.PostDateTime = new DateTime(1900, 1, 1);
inv.GenerateInd = "N";
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
//inv.DocNo = inv.SequenceId.ToString();
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(inv);
C2Setup.SetNextNo("", "AP-PAYMENT-Cash", inv.DocNo, voucher_date);
}
catch
{
}
#endregion
#region det
if (dhc > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, dhc, "DHC", contNo, jobNo, tripId);
}
if (weight > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, weight, "WEIGHING", contNo, jobNo, tripId);
}
if (washing > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, washing, "WASHING", contNo, jobNo, tripId);
}
if (repair > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, repair, "REPAIR", contNo, jobNo, tripId);
}
if (detention > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, detention, "DETENTION", contNo, jobNo, tripId);
}
if (demurrage > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, demurrage, "DEMURRAGE", contNo, jobNo, tripId);
}
if (lift > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, lift, "LIFT ON/OFF", contNo, jobNo, tripId);
}
if (c_Shipment > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, c_Shipment, "C/SHIPMENT", contNo, jobNo, tripId);
}
if (emf > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, emf, "EMF", contNo, jobNo, tripId);
}
if (other > 0)
{
Insert_Det(inv.SequenceId, inv.DocNo, inv.DocType, 1, other, "OTHER", contNo, jobNo, tripId);
}
#endregion
str = jobNo + " | " + contNo + " | " + cntType;
UpdateApPayment(inv.SequenceId);
id = inv.SequenceId;
}
else
{
str += "\n" + jobNo + " | " + contNo + " | " + cntType;
sql = string.Format(@"select top 1 SequenceId from XAApPayment where PartyTo='CASH' and OtherPartyName='{0}' and convert(nvarchar(100),DocDate,23)='{1}' order by sequenceid desc", driver, voucher_date.ToString("yyyy-MM-dd"));
id = SafeValue.SafeInt(Helper.Sql.One(sql), 0);
#region det
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.XAApPayment), "SequenceId=" + id + "");
C2.XAApPayment inv1 = C2.Manager.ORManager.GetObject(query) as C2.XAApPayment;
if (dhc > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, dhc, "DHC", contNo, jobNo, tripId);
}
if (weight > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, weight, "WEIGHING", contNo, jobNo, tripId);
}
if (washing > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, washing, "WASHING", contNo, jobNo, tripId);
}
if (repair > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, repair, "REPAIR", contNo, jobNo, tripId);
}
if (detention > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, detention, "DETENTION", contNo, jobNo, tripId);
}
if (demurrage > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, demurrage, "DEMURRAGE", contNo, jobNo, tripId);
}
if (lift > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, lift, "LIFT ON/OFF", contNo, jobNo, tripId);
}
if (c_Shipment > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, c_Shipment, "C/SHIPMENT", contNo, jobNo, tripId);
}
if (emf > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, emf, "EMF", contNo, jobNo, tripId);
}
if (other > 0)
{
Insert_Det(inv1.SequenceId, inv1.DocNo, inv1.DocType, 1, other, "OTHER", contNo, jobNo, tripId);
}
#endregion
inv1.Remark = "";//str;
C2.Manager.ORManager.StartTracking(inv1, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(inv1);
UpdateApPayment(id);
}
}
else
{
//sql = string.Format(@"select SequenceId,DocId,DocType from XAApPayment where PartyTo='{0}' and DocDate='{1}'", driver, DateTime.Now);
//DataTable in = ConnectSql.GetTab(sql);
//int id = SafeValue.SafeInt(Helper.Sql.One(sql), 0);
}
#endregion
}
}
}
