private void TransferToDo(string doNo, string sku, string lotNo, int qty, decimal price, string wh, string type, int id, string loc)
{
string sql = "";
if (type == "SO")
{
#region SO
sql = @"Insert Into Wh_TransDet(DoNo, DoType,ProductCode,Qty1,Qty2,Qty3,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,DocAmt,LocationCode)";
sql += string.Format(@"select '{0}'as DoNo, 'SO' as DoType,'{1}' as Product
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
,'{3}' as Price
,'{4}' as LotNo
,Uom1,Uom2,Uom3,Uom4
,QtyPackWhole,QtyWholeLoose,QtyLooseBase
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
,att1,att2,att3,att4,att5,att6,Des1,Packing,{2}*{3} as DocAmt , '{6}' LocationCode
from wh_dodet det inner join Wh_DO mast on det.DoNo=mast.DoNo where ProductCode='{1}' and LotNo='{4}' and mast.dotype='IN'", doNo, sku, qty, price, lotNo, EzshipHelper.GetUserName(), wh);
ConnectSql.ExecuteSql(sql);
#endregion
}
if (type == "OUT")
{
#region DOOUT
sql = @"Insert Into Wh_DoDet(JobStatus,DoNo, DoType,ProductCode,Qty4,Qty5,Qty1,Qty2,Qty3,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,DocAmt,LocationCode)";
sql += string.Format(@"select 'Picked','{0}'as DoNo, 'OUT' as DoType,'{1}' as ProductCode
,{2} as Qty4,0 as Qty5
,{2} as Qty1
,Qty2 as Qty2
,Qty3 as Qty3
,{3} as Price
,'{4}' as LotNo
,Uom1,Uom2,Uom3,Uom4
,QtyPackWhole,QtyWholeLoose,QtyLooseBase
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
,att1,att2,att3,att4,att5,att6,Des1,Packing,{2}*{3} as DocAmt , '{6}' LocationCode
from (select * from Wh_DoDet2 where Id={7} ) as tab select @@identity", doNo, sku, qty, price, lotNo, EzshipHelper.GetUserName(), wh, id);
int res = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
sql = @"Insert Into Wh_DoDet2(DoNo,DoType,Product,Qty1,Qty2,Qty3,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,Location,ProcessStatus,PalletNo,ContainerNo,Remark,RelaId)";
sql += string.Format(@"select '{1}'as DoNo, 'OUT' as DoType,'{2}' as Sku
,'{3}' as Qty1
,Qty2
,Qty3
,'{4}' as Price
,'{5}' as LotNo
,Uom1,Uom2,Uom3,Uom4
,QtyPackWhole,QtyWholeLoose,QtyLooseBase
,'{6}' as CreateBy,getdate() as CreateDateTime,'{6}' as UpdateBy,getdate() as UpdateDateTime
,Att1,Att2,Att3,Att4,Att5,Att6,Des1,'' as Packing,'{7}' as Location,'Delivered' as ProcessStatus,PalletNo,ContainerNo,Remark,{8}
from Wh_DoDet where Id={0}", res, doNo, sku, qty, price, lotNo, EzshipHelper.GetUserName(), loc, id);
ConnectSql.ExecuteSql(sql);
#endregion
}
}
private string Save_Inline(int i, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
ASPxTextBox txt_Id = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["Id"], "txt_Id") as ASPxTextBox;
ASPxTextBox txt_ContNo = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ContNo"], "txt_ContNo") as ASPxTextBox;
ASPxComboBox cbbContType = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ContType"], "cbbContType") as ASPxComboBox;
ASPxSpinEdit spin_Price = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["Price"], "spin_Price") as ASPxSpinEdit;
ASPxTextBox txt_Unit = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["Unit"], "txt_Unit") as ASPxTextBox;
ASPxSpinEdit spin_Qty = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["Qty"], "spin_Qty") as ASPxSpinEdit;
ASPxSpinEdit spin_LineIndex = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["LineIndex"], "spin_LineIndex") as ASPxSpinEdit;
ASPxComboBox cbb_ChgCode = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ChgCode"], "cbb_ChgCode") as ASPxComboBox;
ASPxTextBox txt_ChgCodeDe = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["ChgCodeDe"], "txt_ChgCodeDe") as ASPxTextBox;
ASPxMemo txt_Remark = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["Remark"], "txt_Remark") as ASPxMemo;
ASPxTextBox txt_GroupBy = this.grid.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.grid.Columns["GroupBy"], "txt_GroupBy") as ASPxTextBox;
int id = SafeValue.SafeInt(txt_Id.Text, 0);
string contNo = "";
string contType = "";
decimal price = SafeValue.SafeDecimal(spin_Price.Value);
decimal qty = SafeValue.SafeDecimal(spin_Qty.Value);
string unit = SafeValue.SafeString(txt_Unit.Text);
string code = SafeValue.SafeString(cbb_ChgCode.Text);
string des = SafeValue.SafeString(txt_ChgCodeDe.Text);
int lineIndex = SafeValue.SafeInt(spin_LineIndex.Value, 0);
string remark = SafeValue.SafeString(txt_Remark.Text);
decimal locAmt = SafeValue.ChinaRound(qty * SafeValue.SafeDecimal(price, 0), 2);
string groupBy = SafeValue.SafeString(txt_GroupBy.Text);
string sql = string.Format(@"update job_cost set ContNo='{0}',ContType='{1}',Price={2},Qty={3},LocAmt={4},Unit='{6}',ChgCode='{7}',ChgCodeDes='{8}',LineIndex={9},Remark='{10}',GroupBy='{11}' where Id={5}",
contNo, contType, price, qty, locAmt, id, unit, code, des, lineIndex, remark, groupBy);
ConnectSql.ExecuteSql(sql);
return("Success");
}
private void UpdateDoDet2(string orderNo, string orderType)
{
string sql = string.Format(@"update wh_dodet2 set wh_dodet2.PartyId=mast.PartyId,wh_dodet2.DoDate=mast.DoDate from wh_do mast where mast.DoNo=wh_dodet2.DoNo and mast.Dotype=wh_dodet2.DoType
and wh_dodet2.doNo='{0}'", orderNo);
ConnectSql.ExecuteSql(sql);
}
protected void grid_Transport_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
if (e.Parameters == "OK")
{
int update_error_rowcount = 0;
for (int i = 0; i < list.Count; i++)
{
try
{
string id = list[i].Id;
string driver = list[i].diver;
string vehicle = list[i].vehicle;
string sql = string.Format(@"update tpt_job set Driver='{1}',VehicleNo='{2}' where Id='{0}'", id, driver, vehicle);
ConnectSql.ExecuteSql(sql);
}
catch { }
}
if (update_error_rowcount > 0)
{
e.Result = update_error_rowcount + " Rows: Status is existing";
}
else
{
btn_Search_Click(null, null);
}
}
}
private string job_close()
{
ASPxLabel Id = this.grid_job.FindEditFormTemplateControl("lb_Id") as ASPxLabel;
ASPxPageControl pageControl = this.grid_job.FindEditFormTemplateControl("pageControl") as ASPxPageControl;
ASPxTextBox txt_JobNo = this.grid_job.FindEditFormTemplateControl("txt_JobNo") as ASPxTextBox;
string sql = "update CTM_Job set StatusCode=case when StatusCode='CLS' then 'USE' else 'CLS' end where Id=" + Id.Text;
if (ConnectSql.ExecuteSql(sql) > 0)
{
sql = string.Format(@"select StatusCode from CTM_Job where Id={0}", Id.Text);
string status = ConnectSql.ExecuteScalar(sql).ToString();
CtmJobEventLog log = new CtmJobEventLog();
log.CreateDateTime = DateTime.Now;
log.Controller = HttpContext.Current.User.Identity.Name;
log.Platform_isWeb();
log.JobNo = txt_JobNo.Text;
log.JobStatus = status;
log.Remark = "Transfer Job Close";
log.log();
return("");
}
return("error");
}
private int InsertTripLog(string jobNo, string jobType, string driver, string status, DateTime logDate, string logTime, string rmk, string tripId)
{
string sql = string.Format(@"INSERT INTO [CTM_TripLog]([JobNo],[JobType],[Driver],[Status],[LogDate],[LogTime],[Remark],[CreateDateTime],[CreateBy],[UpdateDateTime],[UpdateBy],[tripId])
VALUES('{0}' ,'{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{8}' ,'{7}' ,'{8}' ,'{7}','{9}')", jobNo, jobType, driver, status, logDate.ToString("yyyy-MM-dd"), logTime, rmk, EzshipHelper.GetUserName(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), tripId);
return(ConnectSql.ExecuteSql(sql));
}
private int AddNewSkuLine(int qty, string doNo, string product, string lotNo, string des, string uom1, int pkg, int unit, string att1
, string att2, string att3, string att4, string att5, string att6, int stk, string uom2, string uom3, decimal price, string packing, string wh)
{
string sql = string.Format(@"select tab_hand.HandQty-isnull(tab_Reserved.ReservedQty,0) as BalQty
from (select product,LotNo,Packing ,sum(isnull(Case when det.DoType='In' then Qty1 else -Qty1 end,0)) as HandQty from wh_dodet2 det inner join wh_do mast on det.DoNo=mast.DoNo and mast.StatusCode!='CNL' group by product,LotNo,Packing) as tab_hand
left join (select productCode as Product,LotNo,sum(Qty5) as ReservedQty from wh_doDet det inner join wh_do mast on det.DoNo=mast.DoNo and (mast.StatusCode='CLS' or mast.StatusCode='RETURN') where det.DoType='Out' group by productCode,LotNo) as tab_Reserved on tab_Reserved.product=tab_hand.product and tab_Reserved.LotNo=tab_hand.LotNo
where tab_hand.Product='{0}'", product, lotNo);
int balQty = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (qty > balQty && balQty != 0)
{
sql = @"Insert Into wh_TransDet(DoNo, DoType,ProductCode,Qty1,Qty2,Qty3,Qty4,Qty5,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,DocAmt,LocationCode)";
sql += string.Format(@"select '{0}'as DoNo, 'SO' as DoType,'{1}' as Sku,'{2}' as Qty1,0 as Qty2,0 as Qty3,0 as Qty4,0 as Qty5,'{3}' as Price,'{4}' as LotNo
,'{5}' as Uom1,'{6}' as Uom2,'{7}' as Uom3,'' as Uom4
,'{8}' as QtyPackWhole,'{9}' as QtyWholeLoose,'{10}' as QtyLooseBase
,'{11}' as CreateBy,getdate() as CreateDateTime,'{11}' as UpdateBy,getdate() as UpdateDateTime
,'{12}' as att1,'{13}' as att2,'{14}' as att3,'{15}' as att4,'{16}' as att5,'{17}' as att6,'{18}' as Des1,'{19}' as Packing,({2}*{3}) as DocAmt,'{20}' as LocationCode
from (select '{1}' as Sku) as tab", doNo, product, (qty - balQty), price, lotNo, uom1, uom2, uom3, pkg, unit, stk, EzshipHelper.GetUserName(), att1, att2, att3, att4, att5, att6, des, packing, wh);
ConnectSql.ExecuteSql(sql);
}
else
{
balQty = qty;
}
return(balQty);
}
public string CreateSO(string id)
{
string userId = HttpContext.Current.User.Identity.Name;
string doNo = "";
doNo = C2Setup.GetNextNo("", "SaleOrders", DateTime.Now);
string sql = string.Format(@"insert into Wh_Trans (DoNo,DoDate,PartyId,PartyName,Pic,PartyAdd,Currency,DoType,DoStatus,ExRate,SalesId,CreateBy,CreateDateTime,UpdateBy,UpdateDateTime
,AgentId,AgentName,AgentAdd,AgentContact,NotifyId,NotifyName)
select top 1 '{1}',GETDATE(),wh.PartyId,PartyName,PartyContact,PartyAdd,'SGD','SO','Draft',1,SalesId,'{2}',GETDATE(),'{2}',GETDATE()
,DoctorId,doc.Name,doc.Address,doc.Contact,Patient,pat.Name
from Wh_Schedule as wh
left outer join Ref_PersonInfo as doc on wh.DoctorId=doc.PartyId and doc.Type='Doctor'
left outer join Ref_PersonInfo as pat on wh.Patient=pat.ICNo and pat.Type='Patient'
where wh.Id='{0}'", id, doNo, userId);
SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
C2Setup.SetNextNo("", "SaleOrders", doNo, DateTime.Now);
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhSchedule), "Id='" + id + "'");
WhSchedule s = C2.Manager.ORManager.GetObject(query) as WhSchedule;
s.DoNo = doNo;
Manager.ORManager.StartTracking(s, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(s);
return(doNo);
}
protected void grid_Trip_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
if (e.Parameters == "Three")
{
check_Trip_Status("0", cbb_Driver1.Text, cbb_StatusCode1.Value.ToString());
check_Trip_Status("0", cbb_Driver2.Text, cbb_StatusCode2.Value.ToString());
check_Trip_Status("0", cbb_Driver3.Text, cbb_StatusCode3.Value.ToString());
string sql = "";
if (cb_Row1.Checked)
{
sql = string.Format(@"insert into CTM_JobDet2 (JobNo,ContainerNo,DriverCode,TowheadCode,Det1Id,Statuscode,TripCode,FromTime,FromCode,ToCode,SubletFlag) values ('{0}','{1}','{2}',(select top 1 Towhead from CTM_DriverLog where DATEDIFF(d,Date,(select ScheduleDate from CTM_JobDet1 where Id='{3}'))=0 and Driver='{2}'),'{3}','{4}','{5}','{6}','{7}','{8}','Y')", lb_JobNo.Text, lb_ContNo.Text, cbb_Driver1.Text, lb_det1Id.Text, cbb_StatusCode1.Value.ToString(), cbb_TripCode1.Text, txt_fromTime1.Text, txt_FromCode1.Text, txt_ToCode1.Text);
ConnectSql.ExecuteSql(sql);
}
if (cb_Row2.Checked)
{
sql = string.Format(@"insert into CTM_JobDet2 (JobNo,ContainerNo,DriverCode,TowheadCode,Det1Id,Statuscode,TripCode,FromTime,FromCode,ToCode,SubletFlag) values ('{0}','{1}','{2}',(select top 1 Towhead from CTM_DriverLog where DATEDIFF(d,Date,(select ScheduleDate from CTM_JobDet1 where Id='{3}'))=0 and Driver='{2}'),'{3}','{4}','{5}','{6}','{7}','{8}','Y')", lb_JobNo.Text, lb_ContNo.Text, cbb_Driver2.Text, lb_det1Id.Text, cbb_StatusCode2.Value.ToString(), cbb_TripCode2.Text, txt_fromTime2.Text, txt_FromCode2.Text, txt_ToCode2.Text);
ConnectSql.ExecuteSql(sql);
}
if (cb_Row3.Checked)
{
sql = string.Format(@"insert into CTM_JobDet2 (JobNo,ContainerNo,DriverCode,TowheadCode,Det1Id,Statuscode,TripCode,FromTime,FromCode,ToCode,SubletFlag) values ('{0}','{1}','{2}',(select top 1 Towhead from CTM_DriverLog where DATEDIFF(d,Date,(select ScheduleDate from CTM_JobDet1 where Id='{3}'))=0 and Driver='{2}'),'{3}','{4}','{5}','{6}','{7}','{8}','Y')", lb_JobNo.Text, lb_ContNo.Text, cbb_Driver3.Text, lb_det1Id.Text, cbb_StatusCode3.Value.ToString(), cbb_TripCode3.Text, txt_fromTime3.Text, txt_FromCode3.Text, txt_ToCode3.Text);
ConnectSql.ExecuteSql(sql);
}
threeRowsDataBind();
}
}
private void GetJobRate(string quoteNo, string clientId, string jobType)
{
string sql = string.Format(@"select * from job_rate where ClientId='{0}'", clientId);
DataTable dt = ConnectSql.GetTab(sql);
string sql_part1 = string.Format(@"insert into job_rate(LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price) values");
sql = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
int id = SafeValue.SafeInt(dt.Rows[i]["Id"], 0);
string LineType = SafeValue.SafeString(dt.Rows[i]["LineType"]);
string LineStatus = SafeValue.SafeString(dt.Rows[i]["LineStatus"]);
string ClientId = SafeValue.SafeString(dt.Rows[i]["ClientId"]);
string BillScope = SafeValue.SafeString(dt.Rows[i]["BillScope"]);
string BillType = SafeValue.SafeString(dt.Rows[i]["BillType"]);
string BillClass = SafeValue.SafeString(dt.Rows[i]["BillClass"]);
string ContSize = SafeValue.SafeString(dt.Rows[i]["ContSize"]);
string ContType = SafeValue.SafeString(dt.Rows[i]["ContType"]);
string ChgCode = SafeValue.SafeString(dt.Rows[i]["ChgCode"]);
string ChgCodeDes = SafeValue.SafeString(dt.Rows[i]["ChgCodeDes"]);
string Remark = SafeValue.SafeString(dt.Rows[i]["Remark"]);
decimal Price = SafeValue.SafeDecimal(dt.Rows[i]["Price"]);
string sql_part2 = string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}',{13})", LineType, LineStatus, quoteNo, jobType, clientId, BillScope, BillType, BillClass, ContSize, ContType, ChgCode, ChgCodeDes, Remark, Price);
sql += sql.Length > 0 ? "," + sql_part2 : sql_part2;
}
if (sql.Length > 0)
{
sql = sql_part1 + sql;
int re = ConnectSql.ExecuteSql(sql);
}
}
protected void grid_Transport_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
if (e.Parameters == "OK")
{
//string DriverCode = btn_DriveCode.Text.Trim();
//if (DriverCode.Length == 0)
//{
// e.Result = "Driver Code is request";
// return;
//}
int update_error_rowcount = 0;
for (int i = 0; i < list.Count; i++)
{
try
{
if (check_Trip_Status(list[i].Id, list[i].DriverCode, list[i].Statuscode))
{
update_error_rowcount++;
continue;
}
string sql = string.Format(@"update CTM_JobDet2 set DriverCode='{0}',TowheadCode='{2}',CfsCode='{3}',ChessisCode='{4}',SubletFlag='{5}',SubletHauliername='{6}',BayCode='{7}' where Id='{1}'", list[i].DriverCode, list[i].jobNo, list[i].TowheadCode, list[i].CfsCode, list[i].ChessisCode, list[i].subletFlag, list[i].subletHaulierName, list[i].BayCode);
ConnectSql.ExecuteSql(sql);
}
catch { }
}
if (update_error_rowcount > 0)
{
e.Result = update_error_rowcount + " Rows Update is Error";
}
}
}
private void Trip_new_auto(string JobNo)
{
string sql = string.Format(@"select * From ctm_job where JobNo='{0}'", JobNo);
DataTable dt = ConnectSql_mb.GetDataTable(sql);
DataRow dr_job = dt.Rows[0];
sql = string.Format(@"with tb1 as (
select * from ctm_jobdet1 where jobno='{0}'
),
tb2 as (
select * from ctm_jobdet2 where jobno='{0}'
)
select Id,ContainerNo from (
select *,(select count(*) from tb2 where tb1.Id=tb2.Det1Id) as tripCount from tb1
) as tb where tripCount=0", JobNo);
dt = ConnectSql_mb.GetDataTable(sql);
sql = string.Format(@"insert into CTM_JobDet2 (JobNo,ContainerNo,DriverCode,TowheadCode,ChessisCode,FromCode,FromDate,FromTime,ToCode,ToDate,ToTime,Det1Id,Statuscode,
BayCode,SubletFlag,StageCode,StageStatus) values");
string values = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
values += (values.Length > 0 ? "," : "") + string.Format("('{0}','{1}','','','','{2}',getdate(),left(convert(nvarchar,getdate(),108),5),'{3}',getdate(),left(convert(nvarchar,getdate(),108),5),'{4}','U','B1','N','Pending','Pending'),('{0}','{1}','','','','{2}',getdate(),left(convert(nvarchar,getdate(),108),5),'{3}',getdate(),left(convert(nvarchar,getdate(),108),5),'{4}','U','B1','N','Pending','Pending')", JobNo, dt.Rows[i]["ContainerNo"], dr_job["PickupFrom"], dr_job["DeliveryTo"], dt.Rows[i]["Id"]);
}
if (values.Length > 0)
{
sql = sql + values;
try
{
int i = ConnectSql.ExecuteSql(sql);
}
catch { }
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
if (Request.QueryString["Sn"] != null)
{
string doNo = SafeValue.SafeString(Request.QueryString["Sn"].ToString());
string type = SafeValue.SafeString(Request.QueryString["type"].ToString());
string whId = SafeValue.SafeString(Request.QueryString["WhId"].ToString());
try
{
for (int i = 0; i < list.Count; i++)
{
int id = SafeValue.SafeInt(list[i].id, 0);
string sku = list[i].sku;
int qty1 = list[i].qty1;
string lotNo = list[i].lotNo;
TransferToDo(doNo, sku, lotNo, qty1, whId, id);
string sql = string.Format(@"update Wh_DoDet set Qty1=Qty5,Qty5=0,JobStatus='Picked' where DoNo='{0}' and Qty5<>0", doNo);
ConnectSql.ExecuteSql(sql);
}
e.Result = "Success";
}
catch { }
}
else
{
e.Result = "Please keyin select party ";
}
}
private void after_saved(string isDefault, string type)
{
if (isDefault.Equals("Y"))
{
string sql = string.Format(@"update CTM_MastData set Name='N' where Type='incentive' and type1='{0}' ", type);
ConnectSql.ExecuteSql(sql);
}
}
protected void gv_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e)
{
string sql = string.Format(@"delete from CTM_JobDet2 where Id='{0}'", SafeValue.SafeString(e.Values["Id"]));
ConnectSql.ExecuteSql(sql);
e.Cancel = true;
search_data();
}
protected void grid_wh_RowInserted(object sender, DevExpress.Web.Data.ASPxDataInsertedEventArgs e)
{
string sql = string.Format(@"select top 1 Id from job_house order by Id desc");
int id = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
sql = string.Format(@"update job_house set LineId={0} where Id={0}", id);
ConnectSql.ExecuteSql(sql);
}
protected void grid_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
{
string code = SafeValue.SafeString(e.NewValues["Code"]);
decimal warningAmt = SafeValue.SafeDecimal(e.NewValues["WarningAmt"], 0);
string sql = string.Format(@"update XXParty set WarningAmt={0} where Code='{1}'", warningAmt, code);
SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
}
protected void grid_Transport_RowUpdated(object sender, DevExpress.Web.Data.ASPxDataUpdatedEventArgs e)
{
if (SafeValue.SafeString(e.NewValues["ContainerNo"]) != SafeValue.SafeString(e.OldValues["ContainerNo"]))
{
string sql = string.Format("Update ctm_JobDet2 set ContainerNo='{1}' where Det1Id='{0}'", e.Keys["Id"], SafeValue.SafeString(e.NewValues["ContainerNo"]));
ConnectSql.ExecuteSql(sql);
}
}
private void Transfer(string no, string doDetId, int qty1, int qty2, int qty3, string toLoc)
{
string sql = @"Insert Into [wh_TransferDet]([TransferNo],[FromLocId],[ToLocId],[Product],[Des1],[LotNo],[Qty1],[Qty2],[Qty3],[Uom1],[Uom2],[Uom3],[Uom4],[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[Price],[Packing],Att1,Att2,Att3,Att4,Att5,Att6,[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime])";
sql += string.Format(@"
select '{4}',Location,'{5}' as ToLoc,[Product],[Des1],[LotNo],'{1}' as [Qty1],'{2}' [Qty2],'{3}' [Qty3],[Uom1],[Uom2],[Uom3],[Uom4],[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[Price],[Packing],Att1,Att2,Att3,Att4,Att5,Att6,'{6}',getdate(),'{6}',getDate()
from wh_dodet2 where Id='{0}'", doDetId, qty1, qty2, qty3, no, toLoc, EzshipHelper.GetUserName());
ConnectSql.ExecuteSql(sql);
}
protected void btn_CreateAll_Click(object sender, EventArgs e)
{
if (this.date_search.Date > new DateTime(2010, 1, 1))
{
string sql = string.Format(@"insert into CTM_DriverLog (date,driver,towhead,IsActive,TeamNo) (select '{0}',Code,TowheaderCode,'Y',TeamNo from CTM_Driver where Code not in(select Driver from CTM_DriverLog where DATEDIFF(d,'{0}',Date)=0) and StatusCode='Active')", this.date_search.Date);
int result = SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
btn_search_Click(null, null);
}
}
private int InsertPay(DateTime docDate, string cur)
{
string counterType = "";
int invId = 0;
#region invoice mast
counterType = "AP-PAYMENT-Job";
C2.XAApPayment inv = new C2.XAApPayment();
inv.DocDate = docDate;
string invN = C2Setup.GetNextNo("", counterType, inv.DocDate);
inv.PartyTo = "PSAC";
inv.DocType = "PS";
inv.DocType1 = "Job";
inv.DocNo = invN.ToString();
string[] currentPeriod = EzshipHelper.GetAccPeriod(inv.DocDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
inv.ChqNo = "GIRO";
//
inv.Remark = "";
inv.CurrencyId = cur;
inv.ExRate = 1;
inv.AcCode = "120401";
inv.AcSource = "CR";
inv.MastRefNo = "";
inv.JobRefNo = "";
inv.ExportInd = "N";
inv.CreateBy = HttpContext.Current.User.Identity.Name;
inv.CreateDateTime = DateTime.Now;
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
invId = inv.SequenceId;
invN = C2Setup.GetNextNo("AP-PAYMENT-" + inv.DocType1);
string sql = string.Format("Update XAApPayment set DocNo='{1}',GenerateInd='Y' where SequenceId='{0}'", invId, invN);
if (ConnectSql.ExecuteSql(sql) > -1)
{
C2Setup.SetNextNo("AP-PAYMENT-" + inv.DocType1, invN);
}
}
catch
{
}
#endregion
return(invId);
}
protected void grid_Cont_RowUpdated(object sender, DevExpress.Web.Data.ASPxDataUpdatedEventArgs e)
{
ASPxGridView grd = (sender as ASPxGridView) as ASPxGridView;
updateJob_By_Date(SafeValue.SafeString(grd.GetMasterRowKeyValue(), "0"));
if (SafeValue.SafeString(e.NewValues["ContainerNo"]) != SafeValue.SafeString(e.OldValues["ContainerNo"]))
{
string sql = string.Format("Update ctm_JobDet2 set ContainerNo='{1}' where Det1Id='{0}'", e.Keys["Id"], SafeValue.SafeString(e.NewValues["ContainerNo"]));
ConnectSql.ExecuteSql(sql);
}
}
private string job_void()
{
ASPxLabel Id = this.grid_job.FindEditFormTemplateControl("lb_Id") as ASPxLabel;
string sql = "update CTM_Job set StatusCode=case when StatusCode='CNL' then 'USE' else 'CNL' end where Id=" + Id.Text;
if (ConnectSql.ExecuteSql(sql) > 0)
{
return("");
}
return("error");
}
protected void btn_Send1_Click(object sender, EventArgs e)
{
string sql = string.Format(@"insert into CTM_DriverMessage (SendTo,Content,StatusCode,SendDate,CreateBy,CreateDate) values ('{0}','{1}','USE',GETDATE(),'{2}',GetDate())", txt_sendTo.Text, txt_Content.Text, HttpContext.Current.User.Identity.Name);
int result = ConnectSql.ExecuteSql(sql);
if (result > 0)
{
string temp = string.Format(@"Had sent to {0}:\n{1}", txt_sendTo.Text, txt_Content.Text);
Response.Write("<script>alert('" + temp + "');</script>");
}
}
protected void grid_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
int index = SafeValue.SafeInt(e.Parameters, -1);
string partyId = SafeValue.SafeString((this.grid.GetDataRow(index))["PartyId"]);
if (partyId.Length > 0)
{
string sql = string.Format("update xxparty set Status=(case when Status='USE' then 'InActive' when Status='InActive' then 'USE' else Status end) where PartyId='{0}'", partyId);
ConnectSql.ExecuteSql(sql);
}
}
private void TransferToDo(string partyId, string sku, string des, decimal price1, DateTime fromDate)
{
string sql = @"Insert Into Ref_ProductPrice(PartyId, Product,Description,Price1,Price2,StatusCode,FromDate,[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime])";
sql += string.Format(@"select '{0}'as PartyId, '{1}' as Product,'{2}' as Description
,'{3}' as Price1
,0 as Price2,'Use' as StatusCode
,'{4}' as FromDate
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
from ref_product where Code='{1}'", partyId, sku, des, price1, fromDate, EzshipHelper.GetUserName());
ConnectSql.ExecuteSql(sql);
}
private void TransferToPo(string poNo, int qty1, int qty2, int qty3, decimal price, string lotNo, string poRequestId, decimal amt)
{
string sql = @"Insert Into wh_TransDet(DoNo, DoType,ProductCode,Price,Packing,Qty1,Qty2,Qty3,Qty4,Qty5,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10,Des1,[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],DoInId,DocAmt)";
sql += string.Format(@" select '{1}' as DoNo, 'Po' as DoType,Product,'{2}' as Price,Packing
,'{3}' as Qty1
,'{4}' as Qty2
,'{5}' as Qty3
,0 as Qty4,0 as Qty5,'{6}' as LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10,Des1,'{7}' as CreateBy,getdate() as CreateDateTime,'{7}' as UpdateBy,getdate() as UpdateDateTime,Id as DoInId,'{8}' from Wh_PORequest where Id='{0}'
", poRequestId, poNo, price, qty1, qty2, qty3, lotNo, EzshipHelper.GetUserName(), amt);
if (ConnectSql.ExecuteSql(sql) > 0)
{
sql = string.Format("Update wh_poRequest set PoNo='{1}' where Id='{0}'", poRequestId, poNo);
ConnectSql.ExecuteSql(sql);
}
}
protected void grid_bkg_RowUpdated(object sender, DevExpress.Web.Data.ASPxDataUpdatedEventArgs e)
{
ASPxGridView grd = sender as ASPxGridView;
ASPxTextBox mkgId = grd.FindEditFormTemplateControl("txtMkgId") as ASPxTextBox;
ASPxSpinEdit wt = grd.FindEditFormTemplateControl("spin_wt2") as ASPxSpinEdit;
ASPxSpinEdit m3 = grd.FindEditFormTemplateControl("spin_m4") as ASPxSpinEdit;
ASPxSpinEdit qty = grd.FindEditFormTemplateControl("spin_pkg2") as ASPxSpinEdit;
ASPxButtonEdit pkgType = grd.FindEditFormTemplateControl("txt_pkgType2") as ASPxButtonEdit;
ASPxMemo mkg = grd.FindEditFormTemplateControl("txt_mkg2") as ASPxMemo;
ASPxMemo des = grd.FindEditFormTemplateControl("txt_des2") as ASPxMemo;
string sql = string.Format("update SeaExportMkg set Qty='{1}',PackageType='{2}',Weight='{3}',Volume='{4}',Marking='{5}',Description='{6}' where SequenceId='{0}'"
, mkgId.Text, qty.Value, pkgType.Text, wt.Value, m3.Value, mkg.Text, des.Text);
ConnectSql.ExecuteSql(sql);
}
public static void log_page(HttpRequest par)
{
string _ip = SafeValue.SafeString(par.ServerVariables.Get("REMOTE_ADDR"));
string _method = SafeValue.SafeString(par.ServerVariables.Get("HTTP_METHOD"));
string _url = SafeValue.SafeString(par.ServerVariables.Get("HTTP_URL"));
string _agent = SafeValue.SafeString(par.ServerVariables.Get("HTTP_USER_AGENT"));
string _auth = par.IsAuthenticated ? "Y" : "N";
string _user = !par.IsAuthenticated ? "Guest" : HttpContext.Current.User.Identity.Name;
if (_url.EndsWith(".aspx") || _url.EndsWith(".ashx"))
{
string sql = string.Format(@"insert into HelperLog_Page (IP,Method,Url,Agent,Auth,[User]) values('{0}','{1}','{2}','{3}','{4}','{5}')", _ip, _method, _url, _agent, _auth, _user);
//throw new Exception(sql);
ConnectSql.ExecuteSql(sql);
}
}
private void InsertLeave(int id, int person, string date1, string date2, string name)
{
string sql = string.Format(@"select Remark,LeaveType,(select Amt from Hr_Quote where PayItem='Leave') as Amt from Hr_Leave where ApproveStatus='Approve' and Person={0} and (Date1>='{1}' and Date2<='{2}')", person, date1, date2);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = "Leave";
string des = SafeValue.SafeString(tabDet.Rows[j]["Remark"]);
decimal payamt = SafeValue.SafeDecimal(tabDet.Rows[j]["Amt"]);
decimal before = SafeValue.SafeDecimal(tabDet.Rows[j]["Amt"]);
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", id, code, des, payamt, name);
ConnectSql.ExecuteSql(sql);
}
}