private static DataTable Pl_Cn(string refN, string refType)
{
DataTable tab = new DataTable("Cn");
tab.Columns.Add("BillNo");
tab.Columns.Add("CustName");
tab.Columns.Add("Amount");
string sql = string.Format(@"SELECT mast.DocNo AS BillNo, dbo.fun_GetPartyName(MAX(mast.PartyTo)) AS CustName, SUM(det.LineLocAmt) AS Amount
FROM XAArInvoice AS mast INNER JOIN XAArInvoiceDet AS det ON mast.SequenceId = det.DocId
WHERE (mast.MastRefNo = '{0}') AND (mast.MastType = '{1}') and mast.DocType='CN' GROUP BY mast.DocNo", refN, refType);
DataTable dt = ConnectSql.GetTab(sql);
tab = dt.Copy();
return(tab);
}
public static DataSet DsPayTs(string orderNo)
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAApPayment), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAApPayment obj = objSet[0] as C2.XAApPayment;
#region Invoice
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
//string mastRefNo = obj.MastRefNo;
row["DocNo"] = obj.DocNo;
row["DocType"] = obj.DocType;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["PartyTo"] = obj.PartyTo;
row["PartyName"] = obj.OtherPartyName;
row["ExRate"] = obj.ExRate;
row["Currency"] = obj.CurrencyId;
row["Remark"] = obj.Remark;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
DataTable details = InitDetailDataTable();
string sql = string.Format(@"select sum(LocAmt) as LocAmt,Remark1 from XAApPaymentDet where PayNo='{0}' group by Remark1", orderNo);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
row1["Rmk"] = SafeValue.SafeString(det.Rows[i]["Remark1"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
details.Rows.Add(row1);
}
row["LocAmt"] = obj.LocAmt;
mast.Rows.Add(row);
#endregion
DataSet set = new DataSet();
set.Tables.Add(mast);
set.Tables.Add(details);
//set.Relations.Add("", mast.Columns["DocNo"], details.Columns["DocNo"]);
return(set);
}
protected void grid_ref_HtmlEditFormCreated(object sender, ASPxGridViewEditFormEventArgs e)
{
if (this.grid_ref.EditingRowVisibleIndex > -1)
{
ASPxPageControl pageControl = this.grid_ref.FindEditFormTemplateControl("pageControl") as ASPxPageControl;
ASPxTextBox txt_AgentName = this.grid_ref.FindEditFormTemplateControl("txt_AgentName") as ASPxTextBox;
ASPxTextBox txt_CrAgentName = this.grid_ref.FindEditFormTemplateControl("txt_CrAgentName") as ASPxTextBox;
ASPxTextBox txt_NvoccAgentName = pageControl.FindControl("txt_NvoccAgentName") as ASPxTextBox;
ASPxTextBox txt_LocalCustName = this.grid_ref.FindEditFormTemplateControl("txt_LocalCustName") as ASPxTextBox;
ASPxTextBox txt_WhName = pageControl.FindControl("txt_WhName") as ASPxTextBox;
ASPxDateEdit refDate = this.grid_ref.FindEditFormTemplateControl("date_RefDate") as ASPxDateEdit;
refDate.ReadOnly = true;
refDate.BackColor = ((DevExpress.Web.ASPxEditors.ASPxTextBox)(this.grid_ref.FindEditFormTemplateControl("txt_RefN"))).BackColor;
txt_LocalCustName.Text = EzshipHelper.GetPartyName(this.grid_ref.GetRowValues(this.grid_ref.EditingRowVisibleIndex, new string[] { "LocalCust" }));
txt_AgentName.Text = EzshipHelper.GetPartyName(this.grid_ref.GetRowValues(this.grid_ref.EditingRowVisibleIndex, new string[] { "AgentId" }));
txt_CrAgentName.Text = EzshipHelper.GetPartyName(this.grid_ref.GetRowValues(this.grid_ref.EditingRowVisibleIndex, new string[] { "CarrierAgentId" }));
txt_NvoccAgentName.Text = EzshipHelper.GetPartyName(this.grid_ref.GetRowValues(this.grid_ref.EditingRowVisibleIndex, new string[] { "NvoccAgentId" }));
txt_WhName.Text = EzshipHelper.GetPartyName(this.grid_ref.GetRowValues(this.grid_ref.EditingRowVisibleIndex, new string[] { "WareHouseId" }));
int oid = SafeValue.SafeInt(this.grid_ref.GetRowValues(this.grid_ref.EditingRowVisibleIndex, new string[] { "Id" }), 0);
if (oid > 0)
{
string userId = HttpContext.Current.User.Identity.Name;
ASPxLabel lab_StatusCode = pageControl.FindControl("lb_JobStatus") as ASPxLabel;
string sql = string.Format("select StatusCode from air_ref where Id={0}", oid);
DataTable dt = ConnectSql.GetTab(sql);
string closeInd = SafeValue.SafeString(Manager.ORManager.ExecuteScalar(sql));
ASPxButton btn_VoidMaster = this.grid_ref.FindEditFormTemplateControl("btn_VoidMaster") as ASPxButton;
ASPxButton btn_CloseJob = this.grid_ref.FindEditFormTemplateControl("btn_CloseJob") as ASPxButton;
if (closeInd == "CLS")
{
btn_CloseJob.Text = "Open Job";
lab_StatusCode.Text = "Close";
}
else if (closeInd == "CNL")
{
btn_VoidMaster.Text = "Unvoid";
lab_StatusCode.Text = "Void";
}
else
{
btn_CloseJob.Text = "Close Job";
lab_StatusCode.Text = "USE";
btn_VoidMaster.Text = "Void";
}
}
}
}
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);
}
}
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);
}
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 cmb_loc_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
string s = cmb_WareHouse.SelectedItem.Value.ToString();
string Sql = "SELECT Id,Code,Name from ref_location where Loclevel='Unit' and WarehouseCode='" + s + "'";
DataSet ds = ConnectSql.GetDataSet(Sql);
cmb_loc.DataSource = ds.Tables[0];
cmb_loc.TextField = "Code";
cmb_loc.ValueField = "Code";
cmb_loc.DataBind();
}
protected void FillLocationCombo(string wh)
{
if (string.IsNullOrEmpty(wh))
{
return;
}
cmb_Location.Items.Clear();
string sql = string.Format(@"select Code,WarehouseCode from ref_location");
DataTable tab = ConnectSql.GetTab(sql);
dsRefLocation.FilterExpression = string.Format("WarehouseCode = '{0}'", wh);
cmb_Location.DataBind();
//DataRow[] foundRows = tab.Select(string.Format("WarehouseCode = '{0}'", wh));
//for (int i=0;i<foundRows.Length;i++)
// cmb_Location.Text = (string)foundRows[i]["Code"];
}
public DataTable GetData()
{
string no = "0";
if (SafeValue.SafeString(Request.QueryString["no"]) != null)
{
no = SafeValue.SafeString(Request.QueryString["no"]);
}
string sql = string.Format(@"select hr.Name,hr.Id,hr.Remark4,pay.FromDate,hr.IcNo,hr.Department,hr.HrRole,tab_bank.BankName,tab_begin.BeginDate,tab_bank.AccNo from Hr_Payroll pay
left join Hr_Person hr on pay.Person=hr.Id
left join (select top 1 BankName,Person,AccNo from Hr_PersonDet3 where IsPayroll='True' order by CreateDateTime desc) as tab_bank on tab_bank.Person=hr.Id
left join(select top 1 BeginDate,Person from Hr_PersonDet1 order by CreateDateTime desc) as tab_begin on tab_begin.Person=hr.Id
where pay.Id={0}", no);
return(ConnectSql.GetTab(sql));
}
protected void btn_search_Click(object sender, EventArgs e)
{
if (search_DateFrom.Date < new DateTime(1900, 1, 1))
{
search_DateFrom.Date = DateTime.Now;
}
string sql = string.Format(@"select [BILLING COMPANY] as Customer,[BILL NUMBER] as BillNo,[BILL ITEM NUMBER] as BillItem,
[ACCOUNT NUMBER] as AccountNo,[BILL DATE] as BillDate,[CONTAINER NUMBER] as ContainerNo,[AMOUNT] as Amount,[FULL VESSEL NAME] as Vessel
from psa_bill p where [CONTAINER NUMBER] not in(select ContainerNo from CTM_JobDet1 det1 inner join CTM_Job job on det1.JobNo=job.JobNo where EtaDate < '{0}' )
and [BILL DATE] > '2018-03-31' and [TARIFF CODE] <> '7999' and [BILL DATE] < '{0}' and [AMOUNT]<>0", search_DateFrom.Date.ToString("yyyy-MM-dd"));
//throw new Exception(sql);
grid.DataSource = ConnectSql.GetTab(sql);
grid.DataBind();
}
private void check_Trip_Status(string id, string driverCode, string status)
{
if (driverCode.Trim().Length == 0)
{
return;
}
if (status == "S" || status == "P")
{
string sql = string.Format(@"select COUNT(*) from CTM_JobDet2 where DriverCode='{0}' and Statuscode='{2}' and Id<>'{1}'", driverCode, id, status);
int result = SafeValue.SafeInt(ConnectSql.GetTab(sql).Rows[0][0], 0);
if (result > 0)
{
throw new Exception("Status:'" + status + "' is existing for " + driverCode);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString["no"] != null && Request.QueryString["no"].ToString().Length > 0)
{
txt_JobNo.Text = Request.QueryString["no"].ToString();
DateTime dt = DateTime.Now;
date_YardExpiry.Value = dt;
date_YardExpiry1.Value = dt;
date_YardExpiry2.Value = dt;
date_YardExpiry3.Value = dt;
date_YardExpiry4.Value = dt;
date_YardExpiry5.Value = dt;
date_YardExpiry6.Value = dt;
date_YardExpiry7.Value = dt;
date_YardExpiry8.Value = dt;
date_YardExpiry9.Value = dt;
string sql = string.Format(@"select YardRef from ctm_job where JobNo='{0}'", txt_JobNo.Text);
string DeportAdd = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
btn_YardAddress.Text = DeportAdd;
btn_YardAddress1.Text = DeportAdd;
btn_YardAddress2.Text = DeportAdd;
btn_YardAddress3.Text = DeportAdd;
btn_YardAddress4.Text = DeportAdd;
btn_YardAddress5.Text = DeportAdd;
btn_YardAddress6.Text = DeportAdd;
btn_YardAddress7.Text = DeportAdd;
btn_YardAddress8.Text = DeportAdd;
btn_YardAddress9.Text = DeportAdd;
rows_init();
}
}
string sql1 = string.Format(@"select top 1 substring(ContainerNo,2,case when len(ContainerNo)>1 then len(ContainerNo)-1 else 0 end) as CurNO
from ctm_jobdet1
where len(ContainerNo)<4 and CHARINDEX(ContainerNo,'c',0)=0 and JobNo='{0}'
order by len(ContainerNo) desc,ContainerNo desc", txt_JobNo.Text);
DataTable dt_cont = ConnectSql.GetTab(sql1);
string res = "0";
if (dt_cont.Rows.Count > 0)
{
res = dt_cont.Rows[0]["CurNO"].ToString();
}
txt_CurNO.Text = res;
}
protected void btn_download_excel_Click(object sender, EventArgs e)
{
string sql = string.Format(@"select * from ref_language order by code");
DataTable dt = ConnectSql.GetTab(sql);
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Aspose.Cells.Style style = workbook.Styles[workbook.Styles.Add()]; //新增样式
//标题
style.HorizontalAlignment = TextAlignmentType.Center; //文字居中
style.Font.Name = "Arial"; //文字字体 ,宋体
style.Font.Size = 18; //文字大小
style.Font.IsBold = false; //粗体
cells[0, 0].PutValue("");
cells[0, 1].PutValue("code");
cells[0, 2].PutValue("English");
cells[0, 3].PutValue("Indonesia");
cells[0, 4].PutValue("Chinese");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
int j = i + 2;
cells[j, 0].PutValue("");
cells[j, 1].PutValue(dr["code"]);
cells[j, 2].PutValue(dr["lgg_en"]);
cells[j, 3].PutValue(dr["lgg_id"]);
cells[j, 4].PutValue(dr["lgg_zh"]);
}
string str_date = DateTime.Now.ToString("yyyyMMddHHmmss");
string path0 = string.Format("~/files/language/language_{0}.xlsx", str_date);
string path = HttpContext.Current.Server.MapPath(path0);//POD_RECORD
//workbook.Save(path);
System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流
byte[] bt = ms.ToArray();
workbook.Save(path);
Response.Redirect(path0.Substring(1));
}
private void DataBind()
{
string sql = string.Format(@"select * from CTM_Driver where StatusCode='Active' and isnull(Code,'')<>''");
search_driver.DataSource = ConnectSql.GetTab(sql);
search_driver.DataBind();
DateTime dtime = DateTime.Now;
search_date.Date = dtime;
//search_date2.Date = dtime;
string time = dtime.ToString("HH:mm");
search_Time1.Text = (dtime.Hour >= 9 ? "09:00" : time);
search_Time2.Text = time;
}
protected void ASPxButton2_Click(object sender, EventArgs e)
{
string sql = string.Format(@"with tb1 as (
select * from Mobile_Control where RoleName='{0}'
)
insert into Mobile_Control(Code,Type,IsActive,RoleName)
select Code,Type,IsActive,'{0}' from Mobile_Control_default as df
where not exists(select * from tb1 where tb1.code=df.Code and tb1.Type=df.Type)", this.ASPxComboBox1.Value.ToString());
ConnectSql.ExecuteScalar(sql);
sql = string.Format(@"delete from Mobile_Control where Id in(
select mc.Id from Mobile_Control as mc
left outer join Mobile_Control_default df on mc.Code=df.Code and df.Type=mc.Type where RoleName='{0}' and df.Id is null
)", this.ASPxComboBox1.Value.ToString());
ConnectSql.ExecuteScalar(sql);
this.DataSource2.FilterExpression = "RoleName='" + this.ASPxComboBox1.Value.ToString() + "' ";
}
protected void btn_search_Click(object sender, EventArgs e)
{
if (search_From.Date < new DateTime(1900, 1, 1))
{
search_From.Date = DateTime.Now.AddDays(-15);
}
if (search_To.Date < new DateTime(1900, 1, 1))
{
search_To.Date = DateTime.Now.AddDays(8);
}
string sql = string.Format(@"select Id,JobNo,JobProgress,Driver,TptDate,TptTime,Cust,CustPic,BkgQty,BkgPkgtype,BkgWt,BkgM3,PickFrm1,DeliveryTo1 from tpt_job
where Driver='{0}' and jobProgress<>'Completed' and jobProgress<>'Canceled' and TptDate between '{1}' and '{2}' order by TptDate,TptTime", search_Driver.Text, search_From.Date, search_To.Date);
//Driver='{0}' and
this.Repeater1.DataSource = ConnectSql.GetTab(sql);
this.Repeater1.DataBind();
}
public static DataTable PrintAWB(string refN, string jobN)
{
string sql = string.Format(@"exec proc_PrintAir_AWB '{0}','{1}','','',''", refN, jobN);
//string sql = "";
//if (jobN.Length>2)
//{
// sql = string.Format("select ref.MAWB,job.* from air_job as job inner join air_ref as ref on job.RefNo=ref.RefNo where job.RefNo='{0}' and job.JobNo='{1}'", refN, jobN);
//}
//else
//{
// sql = string.Format("select * from air_ref where RefNo='{0}'", refN);
//}
DataTable tab_mast = ConnectSql.GetTab(sql);
tab_mast.TableName = "Mast";
return(tab_mast);
}
private static DataTable Pl_Cost_house(string refN, string jobNo, string refType, int jobCnt, decimal m3Percent)
{
DataTable tab = new DataTable("Costing");
tab.Columns.Add("Des");
tab.Columns.Add("Amount");
string sql = string.Format(@"SELECT ChgCodeDes+Remark as Des
,Cast(case when JobNo='{1}' then CostLocAmt
when SplitType='Set' and (JobNo='' or JobNo='0') then CostLocAmt/{2}
when SplitType!='Set' and (JobNo='' or JobNo='0') then CostLocAmt*{3}
end as numeric(38,2)) as Amount
FROM Air_Costing Where RefNo='{0}' and (JobNo='{1}' OR JobNo='' or JobNo='0') and JobType='{4}'", refN, jobNo, jobCnt, m3Percent, refType);
DataTable dt = ConnectSql.GetTab(sql);
tab = dt.Copy();
return(tab);
}
private void TransferToDo(string doNo, string sku, string lotNo, int qty, string wh, int id)
{
string 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)";
sql += string.Format(@"select '{0}'as DoNo, 'OUT' as DoType,'{1}' as Sku
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
, Price
,'{3}' as LotNo
,Uom1,Uom2,Uom3,Uom4
,QtyPackWhole,QtyWholeLoose,QtyLooseBase
,'{4}' as CreateBy,getdate() as CreateDateTime,'{4}' as UpdateBy,getdate() as UpdateDateTime
,att1,att2,att3,att4,att5,att6,Des1,Packing,'{5}' as Location,'Delivered' as ProcessStatus
from wh_dodet where Id='{6}'", doNo, sku, qty, lotNo, EzshipHelper.GetUserName(), wh, id);
ConnectSql.ExecuteSql(sql);
}
protected void btn_submit_AddNewStageRow_Click(object sender, EventArgs e)
{
if (btn_DriverCode.Text == "")
{
ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>addNewStageRow_result('-1');</script>");
return;
}
string sql = string.Format(@"insert into CTM_JobDet2 (JobNo,ContainerNo,DriverCode,TowheadCode,ChessisCode,Remark,FromCode,FromDate,FromTime,CfsCode,BayCode,SubletHauliername,SubletFlag,Statuscode,TripCode,Det1Id,StageCode,StageStatus,LoadCode,ToDate,ToTime)
(select JobNo,ContainerNo,'{1}','{2}','{3}','{4}','{5}','{6}','{7}',CfsCode,BayCode,SubletHauliername,SubletFlag,'U',TripCode,Det1Id,'{8}','{9}','{10}','{11}','{12}' from CTM_JobDet2 where Id='{0}' )", txt_Det2Id.Text, btn_DriverCode.Text, txt_TowheadCode.Text, btn_ChessisCode.Text, txt_Trip_Remark.Text, txt_Trip_FromCode.Text, txt_FromDate.Date.ToString("yyyy/MM/dd"), txt_FromTime.Text, cbb_StageCode.Value, cbb_StageStatus.Value, cbb_LoadCode.Value, txt_ToDate.Date.ToString("yyyy/MM/dd"), txt_ToTime.Text);
int result = ConnectSql.ExecuteSql(sql);
if (result == 1)
{
search_data();
}
ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>addNewStageRow_result('" + result + "');</script>");
}
protected void ASPxGridView1_HtmlEditFormCreated(object sender, ASPxGridViewEditFormEventArgs e)
{
if (this.ASPxGridView1.EditingRowVisibleIndex > -1)
{
ASPxComboBox docType1 = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType1") as ASPxComboBox;
docType1.ReadOnly = true;
docType1.BackColor = System.Drawing.Color.FromArgb(255, 240, 240, 240);
int oid = SafeValue.SafeInt(this.ASPxGridView1.GetRowValues(this.ASPxGridView1.EditingRowVisibleIndex, new string[] { "Id" }), 0);
string generateInd = SafeValue.SafeString(ConnectSql.ExecuteScalar("SELECT GenerateInd FROM pay_doc WHERE Id='" + oid + "'"));
if (generateInd == "Y")
{
ASPxDateEdit docDate = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocDt") as ASPxDateEdit;
docDate.BackColor = System.Drawing.Color.FromArgb(255, 240, 240, 240);
docDate.ReadOnly = true;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string refNo = SafeValue.SafeString(Request.QueryString["no"]);
string type = SafeValue.SafeString(Request.QueryString["type"]);
lbl_JobType.Text = type;
string sql = string.Format(@"select * from JobInfo where JobNo='{0}'", refNo);
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
if (this.grid.GetRow(0) != null)
{
this.grid.StartEdit(0);
}
}
}
private void TransferToDo(string doNo, string sku, string lotNo, int qty, string location, decimal price)
{
string 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)";
sql += string.Format(@"select '{0}'as DoNo, 'OUT' as DoType,'{1}' as Sku
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
,'{3}' as Price
,'{4}' as LotNo
,ref.UomPacking as Uom1,ref.UomWhole as Uom2,ref.UomLoose as Uom3,ref.UomBase as Uom4
,ref.QtyPackingWhole as QtyPackWhole,ref.QtyWholeLoose as QtyWholeLoose,ref.QtyLooseBase as QtyLooseBase
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
,ref.Att4 as Att1,ref.Att5 as Att2,ref.att6 as Att3,ref.att7 as Att4,ref.att8 as Att5,ref.att9 as Att6,ref.Description as Des1,'' as Packing,'{6}' as Location,'Delivered' as ProcessStatus
from (select '{1}' as Sku) as tab inner join ref_product ref on ref.Code=tab.Sku", doNo, sku, qty, price, lotNo, EzshipHelper.GetUserName(), location);
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);
}
}
protected void btn_Refresh_Click(object sender, EventArgs e)
{
//string driver = "";
string tr_no = "";//txt_TrailerNo.Text.Trim();
DateTime d1 = date_From.Date;
DateTime d2 = date_To.Date;
string sql_where = string.Format(@"
select * from ctm_mastdata where IsNull(note2,'')='N' and date6 >= '{0:yyyy-MM-dd}' and date6 < '{1:yyyy-MM-dd}' order by date6, note4, remark"
, d1, d2.AddDays(1));
DataTable dt_trip = ConnectSql.GetTab(sql_where);
this.grid_Trailer.DataSource = dt_trip;
this.grid_Trailer.DataBind();
}
private void TransferToPoRequest(string doNo, string sku, int qty, decimal price)
{
string sql = @"Insert Into Wh_PoRequest(SoNo,Product,Qty1,Qty2,Qty3,Price,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing)";
sql += string.Format(@"select '{0}'as DoNo, '{1}' as Sku
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
,'{3}'
,p.UomPacking,p.UomWhole,p.UomLoose,p.UomBase
,p.QtyPackingWhole,p.QtyWholeLoose,p.QtyLooseBase
,'{4}' as CreateBy,getdate() as CreateDateTime,'{4}' as UpdateBy,getdate() as UpdateDateTime
,P.att4,P.att5,P.att6,P.att7,P.att8,P.att9,p.Description,p.Att1
from (select '{1}' as Sku) as tab
left join ref_product p on tab.Sku=p.Code", doNo, sku, qty, price, EzshipHelper.GetUserName());
ConnectSql.ExecuteSql(sql);
}
protected void btn_search_Click(object sender, EventArgs e)
{
string where = "";
string sql = string.Format(@"select DISTINCT d.DoNo,DoStatus,dbo.fun_GetPartyName(d.PartyId) as PartyName,
TotalQty,d.Currency,d.ExRate,d.PayTerm,d.IncoTerm,d.WareHouseId,d.DoDate,
(select SUM(DocAmt) from Wh_TransDet det where det.DoNo=d.DoNo GROUP BY DET.DoNo) as TotalAmt,
isnull((select sum(det1.Qty5) from Wh_DoDet det1 inner join Wh_DO do on do.PoNo=d.DoNo and do.DoNo=det1.DoNo where det1.DoType='OUT'),0) as PickQty,
isnull((select sum(det1.Qty1) from Wh_DoDet det1 inner join Wh_DO do on do.PoNo=d.DoNo and do.DoNo=det1.DoNo where det1.DoType='OUT'),0) as ShipQty,
isnull((select cast(sum(inv.Qty) as numeric(10,0)) from XAArInvoiceDet inv inner join Wh_TransDet det on REPLACE(inv.ChgCode,'(SKU)','')=det.ProductCode and det.DoType='BS' and inv.MastRefNo=det.DoNo where inv.DocType='IV' and inv.MastRefNo=d.DoNo),0) as BillQty
,TotalQty-ISNULL(SoQty,0) as OutstandingQty
from Wh_Trans d left join Wh_TransDet det on d.DoNo=det.DoNo
left join (select SUM(Qty1) as TotalQty,DoNo from Wh_TransDet where DoType='BS' group by DoNo) as tab_total on tab_total.DoNo=d.DoNo
left join (select sum(Qty1) as SoQty,mast.BlanketNo from wh_TransDet det inner join Wh_Trans mast on det.DoNo=mast.DoNo and mast.DoType='SO' and mast.DoStatus='Confirmed' group by mast.BlanketNo) as tab_blanket on tab_blanket.BlanketNo=d.DoNo");
string dateFrom = "";
string dateTo = "";
if (txt_from.Value != null && txt_end.Value != null)
{
dateFrom = txt_from.Date.ToString("yyyy-MM-dd");
dateTo = txt_end.Date.AddDays(1).ToString("yyyy-MM-dd");
}
if (txt_PoNo.Text.Trim() != "")
{
where = GetWhere(where, "d.DoNo='" + txt_PoNo.Text.Trim() + "'");
}
else if (this.txt_CustId.Text.Length > 0)
{
where = GetWhere(where, "PartyId='" + this.txt_CustId.Text.Trim() + "'");
}
if (dateFrom.Length > 0 && dateTo.Length > 0)
{
where = GetWhere(where, " DoDate >= '" + dateFrom + "' and DoDate < '" + dateTo + "'");
}
if (cmb_Type.Text.Trim() != "")
{
where = GetWhere(where, " DoStatus = '" + cmb_Type.Text + "'");
}
if (where.Length > 0)
{
sql += " where " + where + " and d.DoType='BS' order by d.DoNo";
}
DataTable tab = ConnectSql.GetTab(sql);
this.grid.DataSource = tab;
this.grid.DataBind();
}
private bool check_Trip_Status(string id, string driverCode, string status)
{
//if (driverCode.Trim().Length == 0)
//{
// return true;
//}
if (status == "S" || status == "P")
{
string sql = string.Format(@"select COUNT(*) from CTM_JobDet2 where DriverCode='{0}' and Statuscode='{2}' and Id<>'{1}'", driverCode, id, status);
int result = SafeValue.SafeInt(ConnectSql.GetTab(sql).Rows[0][0], 0);
if (result > 0)
{
return(true);
}
}
return(false);
}
protected void btn_search_Click(object sender, EventArgs e)
{
string sql = string.Format(@"select * From psa_bill where 1=1");
if (search_from.Date != null)
{
sql += " and F1>='" + search_from.Date.ToString("yyyyMMdd") + "'";
}
if (search_to.Date != null)
{
sql += " and F1<'" + search_to.Date.AddDays(1).ToString("yyyyMMdd") + "'";
}
DataTable dt = ConnectSql.GetTab(sql);
gv.DataSource = dt;
gv.DataBind();
}
protected void btn_Add_Click(object sender, EventArgs e)
{
string sql = string.Format(@"insert into job_house (BookingNo,HblNo,Weight,Volume,Qty,DgClass,ConsigneeInfo,ConsigneeContact,ConsigneeEmail,ConsigneeTel,ConsigneeZip,ConsigneeAddress,Remark1,JobNo,RefNo,CargoStatus,CargoType,JobType,LineId,SendMode,ClientId,ClientContact,ClientEmail,ClientTel,ClientZip)
values");
string values = "";
string jobNo = "";
if (btn_ClientId.Text.Trim().Length > 0)
{
jobNo = add_job(btn_ClientId.Text);
values += (values.Length > 0 ? "," : "") + string.Format(@"('{0}','{1}',{2},{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{13}','P','IN','WGR',0,'{14}','{15}','{16}','{17}','{18}','{19}')", txt_BookingNo.Text, txt_HblNo.Text, spin_Weight.Value, spin_Volume.Value, spin_Qty.Value, cbb_DgClass.Value,
btn_ConsigneeId.Text, txt_ConsigneeContact.Text, txt_ConsigneeEmail.Text,
txt_ConsigneeTel.Text, txt_ConsigneeZip.Text, memo_ConsigneeAddress.Text,
txt_Remark.Text, jobNo, cbb_SendMode.Text, btn_ClientId.Text, txt_ClientContact.Text, txt_ClientEmail.Text, txt_ClientTel.Text, txt_ClientZip.Text);
}
if (btn_ClientId1.Text.Trim().Length > 0)
{
jobNo = add_job(btn_ClientId1.Text);
values += (values.Length > 0 ? "," : "") + string.Format(@"('{0}','{1}',{2},{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{13}','P','IN','WGR',0,'{14}','{15}','{16}','{17}','{18}','{19}')", txt_BookingNo1.Text, txt_HblNo1.Text, spin_Weight1.Value, spin_Volume1.Value, spin_Qty1.Value, cbb_DgClass1.Value, btn_ConsigneeId1.Text, txt_ConsigneeContact1.Text, txt_ConsigneeEmail1.Text, txt_ConsigneeTel1.Text, txt_ConsigneeZip1.Text, memo_ConsigneeAddress1.Text, txt_Remark1.Text, jobNo, cbb_SendMode1.Text, btn_ClientId1.Text, txt_ClientContact1.Text, txt_ClientEmail1.Text, txt_ClientTel1.Text, txt_ClientZip1.Text);
}
if (btn_ClientId2.Text.Trim().Length > 0)
{
jobNo = add_job(btn_ClientId2.Text);
values += (values.Length > 0 ? "," : "") + string.Format(@"('{0}','{1}',{2},{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{13}','P','IN','WGR',0,'{14}','{15}','{16}','{17}','{18}','{19}')", txt_BookingNo2.Text, txt_HblNo2.Text, spin_Weight2.Value, spin_Volume2.Value, spin_Qty2.Value, cbb_DgClass2.Value, btn_ConsigneeId2.Text, txt_ConsigneeContact2.Text, txt_ConsigneeEmail2.Text, txt_ConsigneeTel2.Text, txt_ConsigneeZip2.Text, memo_ConsigneeAddress2.Text, txt_Remark2.Text, jobNo, cbb_SendMode2.Text, btn_ClientId2.Text, txt_ClientContact2.Text, txt_ClientEmail2.Text, txt_ClientTel2.Text, txt_ClientZip2.Text);
}
if (btn_ClientId3.Text.Trim().Length > 0)
{
jobNo = add_job(btn_ClientId3.Text);
values += (values.Length > 0 ? "," : "") + string.Format(@"('{0}','{1}',{2},{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{13}','P','IN','WGR',0,'{14}','{15}','{16}','{17}','{18}','{19}')", txt_BookingNo3.Text, txt_HblNo3.Text, spin_Weight3.Value, spin_Volume3.Value, spin_Qty3.Value, cbb_DgClass3.Value, btn_ConsigneeId3.Text, txt_ConsigneeContact3.Text, txt_ConsigneeEmail3.Text, txt_ConsigneeTel3.Text, txt_ConsigneeZip3.Text, memo_ConsigneeAddress3.Text, txt_Remark3.Text, jobNo, cbb_SendMode3.Text, btn_ClientId3.Text, txt_ClientContact3.Text, txt_ClientEmail3.Text, txt_ClientTel3.Text, txt_ClientZip2.Text);
}
string re = "false";
if (values.Length > 0)
{
sql = sql + values;
int i = ConnectSql.ExecuteSql(sql);
if (i > 0)
{
re = "success";
update_Line();
//Trip_new_auto("");
}
}
Response.Write("<script>parent.Popup_BookingBatchAdd_callback('" + re + "');</script>");
}
