protected void btn_CreateInv_Init(object sender, EventArgs e)
{
ASPxButton btn = sender as ASPxButton;
GridViewDataItemTemplateContainer container = btn.NamingContainer as GridViewDataItemTemplateContainer;
string jobNo = SafeValue.SafeString(this.grid_Transport.GetRowValues(container.VisibleIndex, "JobNo"));
string sql = "";
string invNo = "";
sql = string.Format(@"select DocNo from XAArInvoice where MastRefNo='{0}' and MastType='CTM'", jobNo);
invNo = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
if (invNo != "")
{
btn.Text = invNo;
btn.ClientSideEvents.Click = String.Format("function (s, e) {{ open_inv_page('{0}'); }}", invNo);
}
else
{
btn.Text = "Create Inv";
btn.ClientInstanceName = String.Format("btn_CreateInv{0}", container.VisibleIndex);
btn.ClientSideEvents.Click = String.Format("function (s, e) {{ inv_create_inline(btn_CreateInv{0},{0}); }}", container.VisibleIndex);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString["type"] != null)
{
search_JobType.Value = SafeValue.SafeString(Request.QueryString["type"]);
cbb_new_jobtype.Value = SafeValue.SafeString(Request.QueryString["type"]);
lbl_type.Text = SafeValue.SafeString(Request.QueryString["type"]);
}
string userId = HttpContext.Current.User.Identity.Name;
string sql_user = string.Format(@"select CustId from [dbo].[User] where Name='{0}'", userId);
string custId = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql_user));
btn_ClientId.Text = custId;
btn_new_ClientId.Text = custId;
txt_search_dateFrom.Date = DateTime.Now.AddDays(-7);
txt_search_dateTo.Date = DateTime.Now;
btn_search_Click(null, null);
EzshipHelper_Authority.Bind_Authority(this.Page);
}
}
public string ReturnDisplay(string driver, string teamNo)
{
string value = "";
if (teamNo == "")
{
value = "block";
}
else
{
string sql_driver = string.Format(@"select count(*) from CTM_Driver where TeamNo='{0}' and Code='{1}'", teamNo, driver);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_driver), 0);
if (n > 0)
{
value = "block";
}
else
{
value = "none";
}
}
return(value);
}
private void TransferToDo(string doNo, string sku, string lotNo, int qty, decimal price, string wh, string type, int id, string loc)
{
string sql = "";
#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
,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 (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
}
protected void Grid1_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
{
ASPxGridView grid = (sender as ASPxGridView) as ASPxGridView;
ASPxComboBox txt_ChgCode = grid.FindEditRowCellTemplateControl(null, "txt_ChgCode") as ASPxComboBox;
e.NewValues["LineType"] = "RATE";
e.NewValues["JobNo"] = "-1";
e.NewValues["ChgCode"] = SafeValue.SafeString(txt_ChgCode.Value);
string sql = string.Format(@"select ChgcodeDes from XXChgCode where ChgcodeId='{0}'", SafeValue.SafeString(txt_ChgCode.Value));
string chgcodeDes = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
e.NewValues["LineStatus"] = Helper.Safe.SafeString(e.NewValues["LineStatus"]).Trim();
e.NewValues["Unit"] = Helper.Safe.SafeString(e.NewValues["Unit"]).Trim();
e.NewValues["ContType"] = Helper.Safe.SafeString(e.NewValues["ContType"]).Trim();
if (Helper.Safe.SafeString(e.NewValues["ContType"]).Trim() != "")
{
e.NewValues["ContSize"] = Helper.Safe.SafeString(e.NewValues["ContType"]).Trim().Substring(0, 2);
}
else
{
e.NewValues["ContSize"] = Helper.Safe.SafeString(e.NewValues["ContSize"]).Trim();
}
e.NewValues["Remark"] = Helper.Safe.SafeString(e.NewValues["Remark"]).Trim();
e.NewValues["BillScope"] = Helper.Safe.SafeString(e.NewValues["BillScope"]).Trim();
e.NewValues["BillType"] = Helper.Safe.SafeString(e.NewValues["BillType"]).Trim();
e.NewValues["BillClass"] = Helper.Safe.SafeString(e.NewValues["BillClass"]).Trim();
e.NewValues["RowCreateUser"] = HttpContext.Current.User.Identity.Name;
e.NewValues["RowCreateTime"] = DateTime.Now;
e.NewValues["RowUpdateUser"] = HttpContext.Current.User.Identity.Name;
e.NewValues["RowUpdateTime"] = DateTime.Now;
if (SafeValue.SafeString(e.NewValues["ChgCodeDe"]) == "")
{
e.NewValues["ChgCodeDe"] = chgcodeDes;
}
btn_search_Click(null, null);
}
private MemoryStream PrintWh_doc(string refN, string refType, string userId)
{
System.IO.MemoryStream str = new MemoryStream();
string user = HttpContext.Current.User.Identity.Name;
XtraReport rpt = new XtraReport();
string path = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("select path from sys_rpt where name='{0}'", refType)));
if (path.Length == 0)
{
return(str);
}
//rpt.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\po.repx"));
rpt.LoadLayout(Server.MapPath(path));
string strsql = string.Format(@"exec proc_PrintWh_Po '{0}','{1}','{2}','{3}','{4}'", refN, "", refType, userId, "");
DataSet ds_temp = ConnectSql.GetDataSet(strsql);
DataTable Mast = ds_temp.Tables[0].Copy();
Mast.TableName = "Mast";
DataTable Detail = ds_temp.Tables[1].Copy();
Detail.TableName = "Detail";
DataTable Detail2 = new DataTable();
if (ds_temp.Tables.Count > 2)
{
Detail2 = ds_temp.Tables[2].Copy();
Detail2.TableName = "Detail";
}
if (Detail.Rows.Count > 0)
{
DevExpress.XtraReports.UI.GroupFooterBand groupFooter_Inv = rpt.Report.Bands["GroupFooter_Wine"] as DevExpress.XtraReports.UI.GroupFooterBand;
DevExpress.XtraReports.UI.XRSubreport subReport_Inv = new XRSubreport();
subReport_Inv.Name = "GroupFooter_Wine1";
groupFooter_Inv.Controls.Add(subReport_Inv);
XtraReport rpt_Inv = new XtraReport();
if (refType == "wh_putaway" || refType == "wh_picking")
{
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\Picking_1.repx"));
}
else if (refType == "wh_doout")
{
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\do_1.repx"));
}
else
{
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\invoice_1.repx"));
}
subReport_Inv.ReportSource = rpt_Inv;
rpt_Inv.DataSource = Detail;
}
if (Detail2.Rows.Count > 0)
{
DevExpress.XtraReports.UI.GroupFooterBand groupFooter_Inv = rpt.Report.Bands["GroupFooter_Other"] as DevExpress.XtraReports.UI.GroupFooterBand;
DevExpress.XtraReports.UI.XRSubreport subReport_Inv = new XRSubreport();
subReport_Inv.Name = "GroupFooter_Other1";
groupFooter_Inv.Controls.Add(subReport_Inv);
XtraReport rpt_Inv = new XtraReport();
if (refType == "wh_putaway" || refType == "wh_picking")
{
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\Picking_2.repx"));
}
else if (refType == "wh_doout")
{
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\do_2.repx"));
}
else
{
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\invoice_2.repx"));
}
subReport_Inv.ReportSource = rpt_Inv;
rpt_Inv.DataSource = Detail2;
}
rpt.DataSource = Mast;
rpt.ExportToPdf(str);
return(str);
}
protected string SaveJob()
{
try
{
ASPxPageControl pageControl = this.grid_Issue.FindEditFormTemplateControl("pageControl_Hbl") as ASPxPageControl;
ASPxTextBox DoNo = grid_Issue.FindEditFormTemplateControl("txt_JobNo") as ASPxTextBox;
ASPxTextBox txt_Id = grid_Issue.FindEditFormTemplateControl("txt_Id") as ASPxTextBox;
string pId = SafeValue.SafeString(txt_Id.Text, "");
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(JobSchedule), "Id='" + pId + "'");
JobSchedule job = C2.Manager.ORManager.GetObject(query) as JobSchedule;
ASPxDateEdit issueDate = grid_Issue.FindEditFormTemplateControl("date_IssueDate") as ASPxDateEdit;
bool isNew = false;
//const string runType = "DOOUT";
string issueN = "";
if (job == null)
{
job = new JobSchedule();
isNew = true;
issueN = C2Setup.GetNextNo("", "JobOrder", issueDate.Date);
job.JobDate = issueDate.Date;
}
ASPxDateEdit doDate = grid_Issue.FindEditFormTemplateControl("date_IssueDate") as ASPxDateEdit;
if (doDate != null)
{
job.JobDate = doDate.Date;
}
ASPxComboBox cmb_Status = grid_Issue.FindEditFormTemplateControl("cmb_Status") as ASPxComboBox;
if (cmb_Status != null)
{
job.JobStage = SafeValue.SafeString(cmb_Status.Value);
}
ASPxComboBox cmb_JobType = grid_Issue.FindEditFormTemplateControl("cmb_JobType") as ASPxComboBox;
if (cmb_JobType != null)
{
job.JobType = SafeValue.SafeString(cmb_JobType.Value);
}
//Main Info
ASPxButtonEdit txt_CustomerId = grid_Issue.FindEditFormTemplateControl("txt_CustomerId") as ASPxButtonEdit;
if (txt_CustomerId != null)
{
job.CustomerId = txt_CustomerId.Text;
}
ASPxTextBox txt_CustomerName = grid_Issue.FindEditFormTemplateControl("txt_CustomerName") as ASPxTextBox;
if (txt_CustomerName != null)
{
job.CustomerName = txt_CustomerName.Text;
}
ASPxMemo memo_Address = grid_Issue.FindEditFormTemplateControl("memo_Address") as ASPxMemo;
if (memo_Address != null)
{
job.CustomerAdd = memo_Address.Text;
}
ASPxTextBox txt_PostalCode = grid_Issue.FindEditFormTemplateControl("txt_PostalCode") as ASPxTextBox;
if (txt_PostalCode != null)
{
job.Postalcode = txt_PostalCode.Text;
}
ASPxTextBox txt_Contact = grid_Issue.FindEditFormTemplateControl("txt_Contact") as ASPxTextBox;
if (txt_Contact != null)
{
job.Contact = txt_Contact.Text;
}
ASPxTextBox txt_Tel = grid_Issue.FindEditFormTemplateControl("txt_Tel") as ASPxTextBox;
if (txt_Tel != null)
{
job.Tel = txt_Tel.Text;
}
ASPxTextBox txt_Email = grid_Issue.FindEditFormTemplateControl("txt_Email") as ASPxTextBox;
if (txt_Email != null)
{
job.Email = txt_Email.Text;
}
ASPxTextBox txt_Fax = grid_Issue.FindEditFormTemplateControl("txt_Fax") as ASPxTextBox;
if (txt_Fax != null)
{
job.Fax = txt_Fax.Text;
}
ASPxMemo remark = grid_Issue.FindEditFormTemplateControl("txt_Remark") as ASPxMemo;
if (remark != null)
{
job.Remark = remark.Text;
}
ASPxButtonEdit currency = grid_Issue.FindEditFormTemplateControl("txt_Currency") as ASPxButtonEdit;
if (currency != null)
{
job.Currency = currency.Text;
}
ASPxSpinEdit exRate = grid_Issue.FindEditFormTemplateControl("spin_ExRate") as ASPxSpinEdit;
if (exRate != null)
{
job.ExRate = SafeValue.SafeDecimal(exRate.Value, 1);
}
ASPxComboBox payTerm = grid_Issue.FindEditFormTemplateControl("cmb_PayTerm") as ASPxComboBox;
if (payTerm != null)
{
job.PayTerm = payTerm.Text;
}
ASPxDateEdit date_ExpiryDate = grid_Issue.FindEditFormTemplateControl("date_ExpiryDate") as ASPxDateEdit;
if (date_ExpiryDate != null)
{
job.ExpiryDate = date_ExpiryDate.Date;
}
ASPxMemo txt_PackRemark = grid_Issue.FindEditFormTemplateControl("txt_PackRemark") as ASPxMemo;
if (txt_PackRemark != null)
{
job.PackRmk = txt_PackRemark.Text;
}
ASPxMemo txt_MoveRemark = grid_Issue.FindEditFormTemplateControl("txt_MoveRemark") as ASPxMemo;
if (txt_MoveRemark != null)
{
job.MoveRmk = txt_MoveRemark.Text;
}
ASPxButtonEdit txt_WareHouseId = grid_Issue.FindEditFormTemplateControl("txt_WareHouseId") as ASPxButtonEdit;
if (txt_WareHouseId != null)
{
job.WareHouseId = txt_WareHouseId.Text;
}
ASPxButtonEdit btn_OriginPort = grid_Issue.FindEditFormTemplateControl("btn_OriginPort") as ASPxButtonEdit;
if (btn_OriginPort != null)
{
job.OriginPort = btn_OriginPort.Text;
}
ASPxButtonEdit btn_DestinationPort = grid_Issue.FindEditFormTemplateControl("btn_DestinationPort") as ASPxButtonEdit;
if (btn_DestinationPort != null)
{
job.DestinationPort = btn_DestinationPort.Text;
}
ASPxMemo memo_Address1 = grid_Issue.FindEditFormTemplateControl("memo_Address1") as ASPxMemo;
if (memo_Address1 != null)
{
job.OriginAdd = memo_Address1.Text;
}
ASPxMemo memo_Address2 = grid_Issue.FindEditFormTemplateControl("memo_Address2") as ASPxMemo;
if (memo_Address2 != null)
{
job.DestinationAdd = memo_Address2.Text;
}
ASPxSpinEdit spin_Volumne = grid_Issue.FindEditFormTemplateControl("spin_Volumne") as ASPxSpinEdit;
if (spin_Volumne != null)
{
job.Volumne = SafeValue.SafeDecimal(spin_Volumne.Value);
}
ASPxMemo memo_Description = grid_Issue.FindEditFormTemplateControl("memo_Description") as ASPxMemo;
if (memo_Description != null)
{
job.ItemDes = memo_Description.Text;
}
ASPxTextBox memo_Volumn = grid_Issue.FindEditFormTemplateControl("memo_Volumn") as ASPxTextBox;
if (memo_Volumn != null)
{
job.VolumneRmk = memo_Description.Text;
}
ASPxSpinEdit spin_HeadCount = grid_Issue.FindEditFormTemplateControl("spin_HeadCount") as ASPxSpinEdit;
if (spin_HeadCount != null)
{
job.HeadCount = SafeValue.SafeInt(spin_HeadCount.Value, 0);
}
ASPxDateEdit date_Pack = grid_Issue.FindEditFormTemplateControl("date_Pack") as ASPxDateEdit;
if (date_Pack != null)
{
job.PackDate = date_Pack.Date;
}
ASPxComboBox cmb_Via = grid_Issue.FindEditFormTemplateControl("cmb_Via") as ASPxComboBox;
if (cmb_Via != null)
{
job.ViaWh = cmb_Via.Text;
}
ASPxDateEdit date_StorageStartDate = grid_Issue.FindEditFormTemplateControl("date_StorageStartDate") as ASPxDateEdit;
if (date_StorageStartDate != null)
{
job.StorageStartDate = date_StorageStartDate.Date;
}
ASPxSpinEdit spin_StorageFreeDays = grid_Issue.FindEditFormTemplateControl("spin_StorageFreeDays") as ASPxSpinEdit;
if (spin_StorageFreeDays != null)
{
job.StorageFreeDays = SafeValue.SafeInt(spin_StorageFreeDays.Value, 0);
}
ASPxSpinEdit spin_STotalDays = grid_Issue.FindEditFormTemplateControl("spin_STotalDays") as ASPxSpinEdit;
if (spin_STotalDays != null)
{
job.StorageTotalDays = Helper.Safe.SafeInt(spin_STotalDays.Value);
}
ASPxTextBox txt_TripNo = grid_Issue.FindEditFormTemplateControl("txt_TripNo") as ASPxTextBox;
if (txt_TripNo != null)
{
job.TripNo = txt_TripNo.Text;
}
ASPxDateEdit date_MoveDate = grid_Issue.FindEditFormTemplateControl("date_MoveDate") as ASPxDateEdit;
if (date_MoveDate != null)
{
job.MoveDate = date_MoveDate.Date;
}
ASPxSpinEdit spin_Charges = grid_Issue.FindEditFormTemplateControl("spin_Charges") as ASPxSpinEdit;
if (spin_Charges != null)
{
job.Charges = SafeValue.SafeDecimal(spin_Charges.Value);
}
ASPxButtonEdit btn_PortOfEntry = grid_Issue.FindEditFormTemplateControl("btn_PortOfEntry") as ASPxButtonEdit;
if (btn_PortOfEntry != null)
{
job.EntryPort = btn_PortOfEntry.Text;
}
ASPxComboBox txt_Mode = grid_Issue.FindEditFormTemplateControl("cmb_Mode") as ASPxComboBox;
if (txt_Mode != null)
{
job.Mode = txt_Mode.Text;
}
ASPxDateEdit date_Eta = grid_Issue.FindEditFormTemplateControl("date_Eta") as ASPxDateEdit;
if (date_Eta != null)
{
job.Eta = date_Eta.Date;
}
ASPxTextBox txt_TruckNo = grid_Issue.FindEditFormTemplateControl("txt_TruckNo") as ASPxTextBox;
if (txt_TruckNo != null)
{
job.TruckNo = txt_TruckNo.Text;
}
ASPxButtonEdit btn_OriginCity = grid_Issue.FindEditFormTemplateControl("btn_OriginCity") as ASPxButtonEdit;
if (btn_OriginCity != null)
{
job.OriginCity = btn_OriginCity.Text;
}
ASPxButtonEdit btn_DestCity = grid_Issue.FindEditFormTemplateControl("btn_DestCity") as ASPxButtonEdit;
if (btn_DestCity != null)
{
job.DestCity = btn_DestCity.Text;
}
ASPxTextBox txt_OriginPostal = grid_Issue.FindEditFormTemplateControl("txt_OriginPostal") as ASPxTextBox;
if (txt_OriginPostal != null)
{
job.OriginPostal = txt_OriginPostal.Text;
}
ASPxTextBox txt_DestPostal = grid_Issue.FindEditFormTemplateControl("txt_DestPostal") as ASPxTextBox;
if (txt_DestPostal != null)
{
job.DestPostal = txt_DestPostal.Text;
}
ASPxComboBox cmb_ServiceType = grid_Issue.FindEditFormTemplateControl("cmb_ServiceType") as ASPxComboBox;
if (cmb_ServiceType != null)
{
job.ServiceType = cmb_ServiceType.Text;
}
//Additional
//ASPxComboBox cmb_FullPacking = pageControl.FindControl("cmb_FullPacking") as ASPxComboBox;
//if (cmb_FullPacking != null)
// job.Item1 = SafeValue.SafeString(cmb_FullPacking.Value);
//ASPxComboBox cmb_UnFull = pageControl.FindControl("cmb_UnFull") as ASPxComboBox;
//if (cmb_UnFull != null)
// job.Item2 = SafeValue.SafeString(cmb_UnFull.Value);
//ASPxTextBox txt_Details = pageControl.FindControl("txt_Details") as ASPxTextBox;
//if (txt_Details != null)
// job.ItemDetail1 = txt_Details.Text;
//ASPxTextBox txt_UnpackDetails = pageControl.FindControl("txt_UnpackDetails") as ASPxTextBox;
//if (txt_UnpackDetails != null)
// job.ItemDetail2 = txt_UnpackDetails.Text;
//ASPxComboBox cmb_Insurance = pageControl.FindControl("cmb_Insurance") as ASPxComboBox;
//if (cmb_Insurance != null)
// job.Item3 = SafeValue.SafeString(cmb_Insurance.Value);
//ASPxTextBox txt_Percentage = pageControl.FindControl("txt_Percentage") as ASPxTextBox;
//if (txt_Percentage != null)
// job.ItemValue3 = txt_Percentage.Text;
//ASPxTextBox txt_Value = pageControl.FindControl("txt_Value") as ASPxTextBox;
//if (txt_Value != null)
// job.ItemData3 = txt_Value.Text;
//ASPxSpinEdit txt_Premium = pageControl.FindControl("txt_Premium") as ASPxSpinEdit;
//if (txt_Premium != null)
// job.ItemPrice3 = SafeValue.SafeDecimal(txt_Premium.Text);
//ASPxComboBox cmb_PianoApply = pageControl.FindControl("cmb_PianoApply") as ASPxComboBox;
//if (cmb_PianoApply != null)
// job.Item4 = SafeValue.SafeString(cmb_PianoApply.Value);
//ASPxTextBox txt_PainoDetails = pageControl.FindControl("txt_PainoDetails") as ASPxTextBox;
//if (txt_PainoDetails != null)
// job.ItemDetail4 = txt_PainoDetails.Text;
//ASPxSpinEdit spin_Charges1 = pageControl.FindControl("spin_Charges1") as ASPxSpinEdit;
//if (spin_Charges1 != null)
// job.ItemPrice4 = SafeValue.SafeDecimal(spin_Charges1.Value);
//ASPxComboBox cmb_Safe = pageControl.FindControl("cmb_Safe") as ASPxComboBox;
//if (cmb_Safe != null)
// job.Item5 = cmb_Safe.Text;
//ASPxTextBox txt_Brand = pageControl.FindControl("txt_Brand") as ASPxTextBox;
//if (txt_Brand != null)
// job.ItemValue5 = SafeValue.SafeString(txt_Brand.Value);
//ASPxSpinEdit spin_Weight = pageControl.FindControl("spin_Weight") as ASPxSpinEdit;
//if (spin_Weight != null)
// job.ItemPrice5 = SafeValue.SafeDecimal(spin_Weight.Value);
//ASPxComboBox cmb_Crating = pageControl.FindControl("cmb_Crating") as ASPxComboBox;
//if (cmb_Crating != null)
// job.Item6 = SafeValue.SafeString(cmb_Crating.Value);
//ASPxTextBox txt_Details1 = pageControl.FindControl("txt_Details1") as ASPxTextBox;
//if (txt_Details1 != null)
// job.ItemDetail6 = txt_Details1.Text;
//ASPxSpinEdit spin_Charges2 = pageControl.FindControl("spin_Charges2") as ASPxSpinEdit;
//if (spin_Charges2 != null)
// job.ItemPrice6 = SafeValue.SafeDecimal(spin_Charges2.Value);
//ASPxComboBox cmb_Handyman = pageControl.FindControl("cmb_Handyman") as ASPxComboBox;
//if (cmb_Handyman != null)
// job.Item7 = SafeValue.SafeString(cmb_Handyman.Value);
//ASPxComboBox cmb_Complimentory = pageControl.FindControl("cmb_Complimentory") as ASPxComboBox;
//if (cmb_Complimentory != null)
// job.ItemValue7 = SafeValue.SafeString(cmb_Complimentory.Value);
//ASPxTextBox txt_Details2 = pageControl.FindControl("txt_Details2") as ASPxTextBox;
//if (txt_Details2 != null)
// job.ItemDetail7 = txt_Details2.Text;
//ASPxSpinEdit spin_Charges3 = pageControl.FindControl("spin_Charges3") as ASPxSpinEdit;
//if (spin_Charges3 != null)
// job.ItemPrice7 = SafeValue.SafeDecimal(spin_Charges3.Value);
//ASPxComboBox cmb_Maid = pageControl.FindControl("cmb_Maid") as ASPxComboBox;
//if (cmb_Maid != null)
// job.Item8 = SafeValue.SafeString(cmb_Maid.Value);
//ASPxComboBox cmb_Complimentory1 = pageControl.FindControl("cmb_Complimentory1") as ASPxComboBox;
//if (cmb_Complimentory1 != null)
// job.ItemValue8 = SafeValue.SafeString(cmb_Complimentory1.Value);
//ASPxTextBox txt_Details3 = pageControl.FindControl("txt_Details3") as ASPxTextBox;
//if (txt_Details3 != null)
// job.ItemDetail8 = txt_Details3.Text;
//ASPxSpinEdit spin_Charges4 = pageControl.FindControl("spin_Charges4") as ASPxSpinEdit;
//if (spin_Charges4 != null)
// job.ItemPrice8 = SafeValue.SafeDecimal(spin_Charges4.Value);
//ASPxComboBox cmb_Shifting = pageControl.FindControl("cmb_Shifting") as ASPxComboBox;
//if (cmb_Shifting != null)
// job.Item9 = SafeValue.SafeString(cmb_Shifting.Value);
//ASPxComboBox cmb_Complimentory2 = pageControl.FindControl("cmb_Complimentory2") as ASPxComboBox;
//if (cmb_Complimentory2 != null)
// job.ItemValue9 = SafeValue.SafeString(cmb_Complimentory2.Value);
//ASPxTextBox txt_Details4 = pageControl.FindControl("txt_Details4") as ASPxTextBox;
//if (txt_Details4 != null)
// job.ItemDetail9 = txt_Details4.Text;
//ASPxSpinEdit spin_Charges5 = pageControl.FindControl("spin_Charges5") as ASPxSpinEdit;
//if (spin_Charges5 != null)
// job.ItemPrice9 = SafeValue.SafeDecimal(spin_Charges5.Value);
//ASPxComboBox cmb_Disposal = pageControl.FindControl("cmb_Disposal") as ASPxComboBox;
//if (cmb_Disposal != null)
// job.Item10 = cmb_Disposal.Text;
//ASPxComboBox cmb_Complimentory3 = pageControl.FindControl("cmb_Complimentory3") as ASPxComboBox;
//if (cmb_Complimentory3 != null)
// job.ItemValue10 = cmb_Complimentory3.Text;
//ASPxTextBox txt_Details5 = pageControl.FindControl("txt_Details5") as ASPxTextBox;
//if (txt_Details5 != null)
// job.ItemDetail10 = txt_Details5.Text;
//ASPxSpinEdit spin_Charges6 = pageControl.FindControl("spin_Charges6") as ASPxSpinEdit;
//if (spin_Charges6 != null)
// job.ItemPrice10 = SafeValue.SafeDecimal(spin_Charges6.Value);
//ASPxComboBox cmb_PickUp = pageControl.FindControl("cmb_PickUp") as ASPxComboBox;
//if (cmb_PickUp != null)
// job.Item11 = SafeValue.SafeString(cmb_PickUp.Value);
//ASPxTextBox txt_Details6 = pageControl.FindControl("txt_Details6") as ASPxTextBox;
//if (txt_Details6 != null)
// job.ItemDetail11 = txt_Details6.Text;
//ASPxComboBox cmb_Additional = pageControl.FindControl("cmb_Additional") as ASPxComboBox;
//if (cmb_Additional != null)
// job.Item12 = cmb_Additional.Text;
//ASPxTextBox txt_Details7 = pageControl.FindControl("txt_Details7") as ASPxTextBox;
//if (txt_Details7 != null)
// job.ItemDetail12 = txt_Details7.Text;
//ASPxComboBox cmb_BadAccess = pageControl.FindControl("cmb_BadAccess") as ASPxComboBox;
//if (cmb_BadAccess != null)
// job.Item13 = SafeValue.SafeString(cmb_BadAccess.Value);
//ASPxComboBox cmb_Origin = pageControl.FindControl("cmb_Origin") as ASPxComboBox;
//if (cmb_Origin != null)
// job.ItemValue13 = cmb_Origin.Text;
//ASPxComboBox cmb_Destination = pageControl.FindControl("cmb_Destination") as ASPxComboBox;
//if (cmb_Destination != null)
// job.ItemData13 = SafeValue.SafeString(cmb_Destination.Value);
//ASPxComboBox cmb_Storage = pageControl.FindControl("cmb_Storage") as ASPxComboBox;
//if (cmb_Storage != null)
// job.Item14 = SafeValue.SafeString(cmb_Storage.Value);
//ASPxComboBox cmb_Complimentory4 = pageControl.FindControl("cmb_Complimentory4") as ASPxComboBox;
//if (cmb_Complimentory4 != null)
// job.ItemValue14 = SafeValue.SafeString(cmb_Complimentory4.Value);
//ASPxTextBox txt_Details8 = pageControl.FindControl("txt_Details8") as ASPxTextBox;
//if (txt_Details8 != null)
// job.ItemDetail14 = txt_Details8.Text;
//ASPxSpinEdit spin_Charges7 = pageControl.FindControl("spin_Charges7") as ASPxSpinEdit;
//if (spin_Charges7 != null)
// job.ItemPrice14 = SafeValue.SafeDecimal(spin_Charges7.Value);
//ASPxTextBox txt_How = pageControl.FindControl("txt_How") as ASPxTextBox;
//if (txt_How != null)
// job.Answer1 = txt_How.Text;
//ASPxTextBox txt_Other = pageControl.FindControl("txt_Other") as ASPxTextBox;
//if (txt_Other != null)
// job.Answer2 = txt_Other.Text;
//ASPxTextBox txt_Move = pageControl.FindControl("txt_Move") as ASPxTextBox;
//if (txt_Move != null)
// job.Answer3 = txt_Move.Text;
//ASPxTextBox txt_UnsuccessRemark = pageControl.FindControl("txt_UnsuccessRemark") as ASPxTextBox;
//if (txt_UnsuccessRemark != null)
// job.Answer4 = txt_UnsuccessRemark.Text;
//ASPxComboBox cmb_WorkStatus = grid_Issue.FindEditFormTemplateControl("cmb_WorkStatus") as ASPxComboBox;
//if (cmb_WorkStatus != null)
// job.WorkStatus = SafeValue.SafeString(cmb_WorkStatus.Value);
//Quotation
//ASPxHtmlEditor txt_Attention1 = pageControl.FindControl("txt_Attention1") as ASPxHtmlEditor;
//if (txt_Attention1 != null)
// job.Attention1 = txt_Attention1.Html;
//ASPxHtmlEditor txt_Attention2 = pageControl.FindControl("txt_Attention2") as ASPxHtmlEditor;
//if (txt_Attention2 != null)
// job.Attention2 = txt_Attention2.Html;
//ASPxHtmlEditor txt_Attention3 = pageControl.FindControl("txt_Attention3") as ASPxHtmlEditor;
//if (txt_Attention3 != null)
// job.Attention3 = txt_Attention3.Html;
//ASPxHtmlEditor txt_Attention4 = pageControl.FindControl("txt_Attention4") as ASPxHtmlEditor;
//if (txt_Attention4 != null)
// job.Attention4 = txt_Attention4.Html;
//ASPxHtmlEditor txt_Attention5 = pageControl.FindControl("txt_Attention5") as ASPxHtmlEditor;
//if (txt_Attention5 != null)
// job.Attention5 = txt_Attention5.Html;
//if (cmb_Status.Text == "Job Confirmation")
//{
// job.WorkStatus = "Pending";
//}
//ASPxComboBox cmb_SalesId = grid_Issue.FindEditFormTemplateControl("cmb_SalesId") as ASPxComboBox;
//if (cmb_SalesId != null)
// job.Value4 = cmb_SalesId.Text;
//if (cmb_Status.Text == "Job Confirmation")
//{
// job.WorkStatus = "Pending";
//}
//if (job.JobStage == "Customer Inquiry")
//{
// job.DateTime1 = DateTime.Now;
//}
//if (job.WorkStatus == "Costing")
//{
// job.DateTime3 = DateTime.Now;
//}
//if (job.WorkStatus == "Quotation")
//{
// job.DateTime4 = DateTime.Now;
//}
//if (job.WorkStatus == "Job Confirmation")
//{
// job.DateTime5 = DateTime.Now;
//}
//if (job.WorkStatus == "Billing")
//{
// job.DateTime6 = DateTime.Now;
//}
//if (job.WorkStatus == "Job Completion")
//{
// job.DateTime7 = DateTime.Now;
//}
//if (job.WorkStatus == "Job Close")
//{
// job.DateTime8 = DateTime.Now;
//}
//ASPxDateEdit date_DateTime2 = grid_Issue.FindEditFormTemplateControl("date_DateTime2") as ASPxDateEdit;
//if (date_DateTime2 != null)
//{
// if (!date_DateTime2.Date.IsDaylightSavingTime())
// job.DateTime2 = date_DateTime2.Date;
// else
// job.DateTime2 = DateTime.Now;
//}
//ASPxMemo memo_Notes = pageControl.FindControl("memo_Notes") as ASPxMemo;
//if (memo_Notes != null)
// job.Notes = memo_Notes.Text;
string userId = HttpContext.Current.User.Identity.Name;
if (isNew)
{
job.CreateBy = userId;
job.CreateDateTime = DateTime.Now;
job.UpdateBy = userId;
job.UpdateDateTime = DateTime.Now;
job.JobNo = issueN;
Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "JobOrder", issueN, issueDate.Date);
}
else
{
job.UpdateBy = userId;
job.UpdateDateTime = DateTime.Now;
bool isAddLog = false;
if (job.JobStatus == SafeValue.SafeString(ConnectSql.ExecuteScalar("Select JobStatus from JobSchedule where JobNo='" + job.JobNo + "'")))
{
}
else
{
isAddLog = true;
}
Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(job);
if (isAddLog)
{
EzshipLog.Log(job.JobNo, "", "JO", job.JobStatus);
}
}
Session["SoWhere"] = "JobNo='" + job.JobNo + "'";
this.dsSchedule.FilterExpression = Session["SoWhere"].ToString();
if (this.grid_Issue.GetRow(0) != null)
{
this.grid_Issue.StartEdit(0);
}
return(job.JobNo);
}
catch (Exception ex) { throw new Exception(ex.Message + ex.StackTrace); }
return("");
}
protected bool UpdateParty(string name, string uen, string ic, string email1, string email2, string tel1, string tel2, string mobile1, string mobile2, string address, string responsible)
{
string sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and (CrNo='{1}' or CrNo='{2}')", name, uen, ic);
int count = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
bool action = false;
if (count > 0)
{
#region Update Party
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
string code = "";
if (name.Length > 4)
{
if (responsible != "个人")
{
code = name.Substring(0, 4) + DateTime.Today.Second;
}
else
{
code = name;
}
}
else
{
code = name;
}
if (ic.Length > 0)
{
#region
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", name, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CrNo", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Value", "like '%" + name + "%'", SqlDbType.NVarChar, 100);
list.Add(cpar);
sql = string.Format(@"update XXParty set Code=@Code,Name=@Name,Address=@Address,Tel1=@Tel1,Tel2=@Tel2,Fax1=@Fax1,Fax2=@Fax2,Email1=@Email1,Email2=@Email2,CrNo=@CrNo where Name=@Value or CrNo=@CrNo");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
#endregion
}
if (uen.Length > 0)
{
#region
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", name, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CrNo", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Value", "like '%" + name + "%'", SqlDbType.NVarChar, 100);
list.Add(cpar);
sql = string.Format(@"update XXParty set Code=@Code,Name=@Name,Address=@Address,Tel1=@Tel1,Tel2=@Tel2,Fax1=@Fax1,Fax2=@Fax2,Email1=@Email1,Email2=@Email2,CrNo=@CrNo where Name=@Value or CrNo=@CrNo");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
#endregion
}
#endregion
}
return(action);
}
public void ImportExcel(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
//Job Info
string client = ws.Cells["B" + 2].StringValue;
string vessel = ws.Cells["B" + 3].StringValue;
string voyage = ws.Cells["D" + 3].StringValue;
string pol = ws.Cells["B" + 4].StringValue;
string pod = ws.Cells["D" + 4].StringValue;
string warehouse = ws.Cells["H" + 2].StringValue;
string isWarehouse = ws.Cells["J" + 2].StringValue;
DateTime now = DateTime.Now;
string jobNo = "";
C2.CtmJob job = new C2.CtmJob();
jobNo = C2Setup.GetNextNo("", "CTM_Job_WGR", DateTime.Now);
job.JobNo = jobNo;
job.JobType = "WGR";
job.JobDate = DateTime.Now;
job.ClientId = EzshipHelper.GetPartyId(client);
job.DeliveryTo = "";
job.StatusCode = "USE";
job.QuoteNo = jobNo;
job.QuoteStatus = "None";
job.JobStatus = "Confirmed";
job.Vessel = vessel;
job.Voyage = voyage;
job.Pol = pol;
job.Pod = pod;
job.WareHouseCode = warehouse;
job.IsWarehouse = isWarehouse;
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_WGR", jobNo, now);
auto_add_trailer_trip(jobNo, "WGR");
//Order Info
string bkgRefNo = ws.Cells["B" + 7].StringValue;
string hblNo = ws.Cells["F" + 7].StringValue;
string conNo = ws.Cells["I" + 7].StringValue;
string shipper = ws.Cells["B" + 8].StringValue;
string consignee = ws.Cells["B" + 9].StringValue;
string ic_uen = ws.Cells["H" + 9].StringValue;
string responsible = ws.Cells["K" + 9].StringValue;
string email1 = ws.Cells["C" + 10].StringValue;
string email2 = ws.Cells["H" + 10].StringValue;
string tel = ws.Cells["C" + 11].StringValue;
string postCode = ws.Cells["E" + 11].StringValue;
string mobile = ws.Cells["H" + 11].StringValue;
string address = ws.Cells["B" + 12].StringValue;
string receiver = ws.Cells["B" + 14].StringValue;
string contact = ws.Cells["B" + 15].StringValue;
string delivery = ws.Cells["B" + 16].StringValue;
string prepaidInd = ws.Cells["C" + 18].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 18].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["K" + 18].StringValue);
string dutyPayment = ws.Cells["B" + 19].StringValue;
string incoterm = ws.Cells["B" + 20].StringValue;
decimal ocean_freight = SafeValue.SafeDecimal(ws.Cells["H" + 20].StringValue);
string remark = ws.Cells["B" + 21].StringValue;
string fee1CurrId = ws.Cells["H" + 24].StringValue;
string totalQty = ws.Cells["B" + 24].StringValue;
string packType = ws.Cells["D" + 2].StringValue;
string weight = ws.Cells["F" + 24].StringValue;
string m3 = ws.Cells["J" + 24].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@HblNo", hblNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@RefNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", EzshipHelper.GetPartyId(shipper), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic_uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (email2.Length > 0)
{
email1 += "," + email2;
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeTel", tel, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeZip", postCode, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeAddress", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientAddress", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", ocean_freight, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeDecimal(totalQty, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "WGR", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,HblNo,JobNo,RefNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,ConsigneeTel,Mobile1,ConsigneeAddress,ClientId,ClientEmail,ClientAddress,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Currency,Qty,PackTypeOrig,Weight,Volume,CargoType)
values (@BookingNo,@HblNo,@JobNo,@RefNo,@JobType,@ContNo,'P',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@ConsigneeTel,@Mobile1,@ConsigneeAddress,@ClientId,@ClientEmail,@ClientAddress,@Remark1,@PrepaidInd,@CollectAmount1,
@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume,'IN') select @@identity");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
if (re.status)
{
string id = SafeValue.SafeString(re.context);
sql = string.Format(@"update job_house set LineId={0} where Id={0}", id);
ConnectSql_mb.ExecuteNonQuery(sql);
//insert_cargo(SafeValue.SafeInt(id, 0));
if (id.Length > 0)
{
action = true;
for (int i = 25; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string B = ws.Cells["B" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0 && B.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", id, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", id);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@B", B, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(E, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeDecimal(F, 0), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", id, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@B,@C,@D,@E,@F,@G,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
//VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string filter = e.Parameters;
ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
//ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
if (filter == "P")
{
SaveBill();
#region Post
string sql = @"SELECT AcYear, AcPeriod, DocType, DocNo, DocDate, DocCurrency, DocExRate, PartyTo, OtherPartyName, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, ExportInd,
BankName, Remark,GenerateInd FROM pay_doc";
sql += " WHERE Id='" + oidCtr.Text + "'";
DataTable dt = Manager.ORManager.GetDataSet(sql).Tables[0];
int acYear = 0;
int acPeriod = 0;
string docN = "";
string docType = "";
string acSource = "";
string acCode = "";
decimal locAmt = 0;
decimal docAmt = 0;
decimal exRate = 0;
string currency = "";
DateTime docDt = DateTime.Today;
string remarks = "";
string partyTo = "";
string otherPartyName = "";
string chqNo = "";
string generateInd = "";
if (dt.Rows.Count == 1)
{
acYear = SafeValue.SafeInt(dt.Rows[0]["AcYear"], 0);
acPeriod = SafeValue.SafeInt(dt.Rows[0]["AcPeriod"], 0);
acSource = dt.Rows[0]["AcSource"].ToString();
acCode = dt.Rows[0]["AcCode"].ToString();
docN = dt.Rows[0]["DocNo"].ToString();
docType = dt.Rows[0]["DocType"].ToString();
locAmt = SafeValue.SafeDecimal(dt.Rows[0]["LocAmt"].ToString(), 0);
docAmt = SafeValue.SafeDecimal(dt.Rows[0]["DocAmt"].ToString(), 0);
exRate = SafeValue.SafeDecimal(dt.Rows[0]["DocExRate"].ToString(), 0);
currency = dt.Rows[0]["DocCurrency"].ToString();
docDt = SafeValue.SafeDate(dt.Rows[0]["DocDate"], new DateTime(1900, 1, 1));
// partyId = dt.Rows[0][""].ToString();
remarks = dt.Rows[0]["Remark"].ToString();
partyTo = dt.Rows[0]["PartyTo"].ToString();
otherPartyName = dt.Rows[0]["OtherPartyName"].ToString();
chqNo = dt.Rows[0]["ChqNo"].ToString();
generateInd = SafeValue.SafeString(dt.Rows[0]["GenerateInd"]);
}
else
{
e.Result = "Can't find the ArReceipt!";
return;
}
if (generateInd.ToUpper() != "Y")
{
e.Result = "Have not generate no!";
return;
}
string sqlDet = string.Format("select count(Id) from pay_line where RepId='{0}'", oidCtr.Text);
int detCnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sqlDet), 0);
if (detCnt == 0)
{
e.Result = "No Detail, Can't Post";
return;
}
sqlDet = string.Format("select max(docDate) from pay_line where RepId='{0}'", oidCtr.Text);
DateTime maxLineDocDate = SafeValue.SafeDate(Manager.ORManager.ExecuteScalar(sqlDet), new DateTime(1900, 1, 1));
if (maxLineDocDate > new DateTime(2000, 1, 1) && maxLineDocDate > docDt)
{
e.Result = "The bill's Date can't be greater than receipt date.";
return;
}
//check account period
if (acYear < 1 || acPeriod < 1)
{
e.Result = "Account year or Period Invalid!";
return;
}
string sql1 = "select CloseInd from XXAccPeriod where Year='" + acYear + "' and Period ='" + acPeriod + "'";
string closeInd = SafeValue.SafeString(Manager.ORManager.ExecuteScalar(sql1), "");
if (closeInd == "")
{
e.Result = "Can't find this account period!";
return;
}
else if (closeInd == "Y")
{
e.Result = "The account period is closed!";
return;
}
//mast.amt det.amt is match
sql = string.Format("select sum(locamt) from pay_line where RepId='{0}' and AcSource='CR'", oidCtr.Text);
decimal amt_detCr = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
sql = string.Format("select sum(locamt) from pay_line where RepId='{0}' and AcSource='DB'", oidCtr.Text);
decimal amt_detDb = SafeValue.SafeDecimal(Manager.ORManager.ExecuteScalar(sql), 0);
if (docType == "RE" && locAmt != amt_detCr - amt_detDb)
{
e.Result = "Amount can't match, can't post";
return;
}
else if (docType == "PC" && locAmt != -amt_detCr + amt_detDb)
{
e.Result = "Amount can't match, can't post";
return;
}
sql = "select count(*) from pay_line where AcCode='' and RepId='" + oidCtr.Text + "'";
detCnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (detCnt > 0)
{
e.Result = "Some Item's Accode is blank, pls check";
return;
}
//delete old post data
sql = string.Format("SELECT SequenceId from XAGlEntry WHERE DocNo='{0}' and DocType='{1}'", docN, docType);
int glOldOid = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql), 0);
if (glOldOid > 0)
{
DeleteGl(glOldOid);
}
//Insert into gl entry
int glOid = 0;
try
{
C2.XAGlEntry gl = new XAGlEntry();
gl.AcPeriod = acPeriod;
gl.AcYear = acYear;
gl.ArApInd = "AR";
gl.DocType = docType;
gl.DocDate = docDt;
gl.DocNo = docN;
gl.CrAmt = docAmt;
gl.DbAmt = docAmt;
gl.CurrencyCrAmt = locAmt;
gl.CurrencyDbAmt = locAmt;
gl.CurrencyId = currency;
gl.EntryDate = DateTime.Now;
gl.ExRate = exRate;
gl.PostDate = DateTime.Now;
gl.PostInd = "N";
gl.Remark = remarks;
gl.UserId = HttpContext.Current.User.Identity.Name;
gl.CancelInd = "N";
gl.CancelDate = new DateTime(1900, 1, 1);
gl.PartyTo = partyTo;
gl.OtherPartyName = otherPartyName;
gl.ChqNo = chqNo;
gl.SupplierBillNo = "";
gl.SupplierBillDate = new DateTime(1900, 1, 1);
Manager.ORManager.StartTracking(gl, InitialState.Inserted);
Manager.ORManager.PersistChanges(gl);
glOid = gl.SequenceId;
//insert Detail
OPathQuery query = new OPathQuery(typeof(pay_line), "RepId='" + oidCtr.Text + "'");
ObjectSet set = Manager.ORManager.GetObjectSet(query);
int index = 1;
XAGlEntryDet det1 = new XAGlEntryDet();
det1.AcCode = acCode;
det1.ArApInd = "AR";
det1.AcPeriod = acPeriod;
det1.AcSource = acSource;
det1.AcYear = acYear;
det1.CrAmt = 0;
det1.CurrencyCrAmt = 0;
det1.DbAmt = docAmt;
det1.CurrencyDbAmt = locAmt;
det1.CurrencyId = currency;
det1.DocNo = docN;
det1.DocType = docType;
det1.ExRate = exRate;
det1.GlLineNo = index;
det1.GlNo = gl.SequenceId;
det1.Remark = remarks;
Manager.ORManager.StartTracking(det1, InitialState.Inserted);
Manager.ORManager.PersistChanges(det1);
for (int i = 0; i < set.Count; i++)
{
try
{
index++;
pay_line invDet = set[i] as pay_line;
XAGlEntryDet det = new XAGlEntryDet();
det.AcCode = invDet.AcCode;
det.ArApInd = "AR";
det.AcPeriod = acPeriod;
det.AcSource = invDet.AcSource;
det.AcYear = acYear;
if (invDet.AcSource == "DB")
{
det.DbAmt = invDet.DocAmt;
det.CurrencyDbAmt = invDet.LocAmt;
det.CrAmt = 0;
det.CurrencyCrAmt = 0;
}
else// if (det.AcSource == "CR")
{
det.CrAmt = invDet.DocAmt;
det.CurrencyCrAmt = invDet.LocAmt;
det.DbAmt = 0;
det.CurrencyDbAmt = 0;
}
det.CurrencyId = invDet.Currency;
det.DocNo = docN;
det.DocType = docType;
det.ExRate = invDet.ExRate;
det.GlLineNo = index;
det.GlNo = gl.SequenceId;
det.Remark = invDet.Remark1;
Manager.ORManager.StartTracking(det, InitialState.Inserted);
Manager.ORManager.PersistChanges(det);
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
}
UpdateArInv(oidCtr.Text);
EzshipLog.Log(docN, "", docType, "Post");
e.Result = "Post completely!";
}
catch
{
e.Result = "Posting Error, Please repost!";
DeleteGl(glOid);
}
#endregion
}
else if (filter == "DD")
{
#region delete receipt
try
{
string sql = "SELECT Id,DocId, DocType FROM pay_line where RepId ='" + oidCtr.Text + "'";
DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string id = tab.Rows[i]["Id"].ToString();
string docId = tab.Rows[i]["DocId"].ToString();
string docType = tab.Rows[i]["DocType"].ToString();
string sql_det = "delete from pay_line where Id='" + id + "'";
if (C2.Manager.ORManager.ExecuteCommand(sql_det) > 0)
{
UpdateMaster(docId, docType);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
else if (filter == "Reverse")
{
#region reverse receipt 0:period is closed? 1:unpost 2:exportind='N' 3: delete all item
try
{
string sql1 = string.Format("select CloseInd from xxaccperiod where Year=(select AcYear from pay_doc where Id='{0}') and Period=(select top(1)AcPeriod from pay_doc where SequenceId='{0}')", oidCtr.Text);
if (SafeValue.SafeString(ConnectSql.ExecuteScalar(sql1)) != "N")
{
throw new Exception("Acc Period is Closed!");
}
string sql_invoice = string.Format("select sequenceid from xaglentry where ArApInd='AR' and docno=(select docno from pay_doc where id='{0}') and DocType=(select doctype from pay_doc where id='{0}') ", oidCtr.Text);
DeleteGl(SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_invoice), 0));
sql_invoice = "update pay_doc set IsReturn='true', ExportInd='N' where Id='" + oidCtr.Text + "'";
Manager.ORManager.ExecuteCommand(sql_invoice);
string sql = "SELECT Id,DocId, DocType FROM pay_line where RepId ='" + oidCtr.Text + "'";
DataTable tab = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
for (int i = 0; i < tab.Rows.Count; i++)
{
try
{
string id = tab.Rows[i]["Id"].ToString();
string docId = tab.Rows[i]["DocId"].ToString();
string docType = tab.Rows[i]["DocType"].ToString();
string sql_det = "delete from pay_line where Id='" + id + "'";
if (C2.Manager.ORManager.ExecuteCommand(sql_det) > 0)
{
UpdateMaster(docId, docType);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
#endregion
}
else if (filter == "GainLoss")
{
#region GAIN LOSS
ASPxComboBox docType = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType") as ASPxComboBox;
string sql = string.Format("select (select LocAmt from pay_doc where Id='{0}')-iSNULL(sum(case when AcSource='CR' then -LocAmt else LocAmt end),0) As LocAmt from pay_line where repid='{0}'", oidCtr.Text);
if (docType.Text == "RE")
{
sql = string.Format("select (select LocAmt from pay_doc where Id='{0}')-iSNULL(sum(case when AcSource='DB' then -LocAmt else LocAmt end),0) As LocAmt from pay_line where repid='{0}'", oidCtr.Text);
}
decimal balAmt = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (balAmt > 0)
{
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
pay_line det = new pay_line();
det.AcCode = gainAccCode;
if (docType.Text == "RE")
{
det.AcSource = "CR";
}
else
{
det.AcSource = "DB";
}
det.DocType = docType.Text;
det.RepType = docType.Text;
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.ExRate = 1;
det.LocAmt = balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.RepId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.RepNo = docN.Text;
string sql_detCnt = "select count(DocNo) from pay_line where RepId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.RepLineNo = lineNo;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
else if (balAmt < 0)
{
string gainAccCode = System.Configuration.ConfigurationManager.AppSettings["GainLoseAcCode"];
pay_line det = new pay_line();
det.AcCode = gainAccCode;
if (docType.Text == "RE")
{
det.AcSource = "DB";
}
else
{
det.AcSource = "CR";
}
det.DocType = docType.Text;
det.RepType = docType.Text;
det.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
det.DocAmt = -balAmt;
det.DocDate = new DateTime(1900, 1, 1);
det.DocId = 0;
det.DocNo = "";
det.ExRate = 1;
det.LocAmt = -balAmt;
det.Remark1 = "GAIN AND LOSS";
det.Remark2 = "";
det.Remark3 = "";
det.RepId = SafeValue.SafeInt(oidCtr.Text, 0);
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
det.RepNo = docN.Text;
string sql_detCnt = "select count(DocNo) from pay_line where RepId='" + oidCtr.Text + "'";
int lineNo = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql_detCnt), 0) + 1;
det.RepLineNo = lineNo;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
#endregion
}
else if (filter == "Generate")
{
string sql = @"SELECT DocType, DocDate FROM pay_doc WHERE Id='" + oidCtr.Text + "'";
DataTable dt = Manager.ORManager.GetDataSet(sql).Tables[0];
if (dt.Rows.Count == 1)
{
DateTime docDate = SafeValue.SafeDate(dt.Rows[0]["DocDate"], DateTime.Today);
string docType = SafeValue.SafeString(dt.Rows[0]["DocType"]);
string invN = C2Setup.GetNextNo(docType, "AR-RECEIPT", docDate);
sql = string.Format("Update pay_doc set DocNo='{1}',GenerateInd='Y' where Id='{0}'", oidCtr.Text, invN);
if (ConnectSql.ExecuteSql(sql) > -1)
{
C2Setup.SetNextNo(docType, "AR-RECEIPT", invN, docDate);
}
e.Result = invN;
}
}
}
public static DataTable dsProfit(DateTime d1, DateTime d2, string salesman, string userName)
{
DataTable det = new DataTable("Det");
det.Columns.Add("PeriodStr");
det.Columns.Add("ImpExpInd");
det.Columns.Add("RefType");
det.Columns.Add("Salesman");
det.Columns.Add("Cnt");
det.Columns.Add("RevAmt");
det.Columns.Add("CostAmt");
det.Columns.Add("ProfitAmt");
det.Columns.Add("UserId");
string sql = string.Format(@"SELECT ref.RefNo, ref.RefType, ref.AirportCode0 as Port, ref.CurrencyRate,ref.FlightDate1 as Eta,ref.FlightDate0 as Etd, job.JobNo, job.CustomerId, job.HAWB, job.Weight, job.Volume,ref.Volume as TotVolume
FROM air_job AS job INNER JOIN air_ref AS ref ON job.RefNo = ref.RefNo
WHERE (ref.Eta >= '{0}') AND (ref.Eta < '{1}')", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"));
sql += " order by job.JobNo";
string periodStr = string.Format("Date From {0} to {1}", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"));
string role = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar("select role from [user] where Name='" + userName + "'"), "").ToUpper();
DataTable tab1 = ConnectSql.GetTab(sql);
for (int i = 0; i < tab1.Rows.Count; i++)
{
try
{
DataRow row = tab1.Rows[i];
string partyId = SafeValue.SafeString(row["CustomerId"], "");
string salesmanId = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("Select SalesmanId from XXParty where PartyId='{0}'", partyId)), "").ToUpper();
salesman = salesman.ToUpper();
if (salesman == "All" || salesman == "NA" || salesman == salesmanId)
{
DataRow rptRow = det.NewRow();
rptRow["PeriodStr"] = periodStr;
rptRow["ImpExpInd"] = "Import";
string refNo = row["RefNo"].ToString();
string jobNo = row["JobNo"].ToString();
string refType = row["JobType"].ToString().ToUpper();
if (refType == "CONSOL")
{
continue;
}
rptRow["RefType"] = refType;
rptRow["Salesman"] = salesmanId;
rptRow["CostAmt"] = 0;
rptRow["RevAmt"] = 0;
string sql_Iv = string.Format("SELECT DocType, sum(LocAmt) Amt FROM XAArInvoice WHERE MastType='{0}' and (MastRefNo = '{1}') and JobRefNo='{2}' group by DocType", refType, refNo, jobNo);
DataTable tab_Iv = ConnectSql.GetTab(sql_Iv);
for (int r = 0; r < tab_Iv.Rows.Count; r++)
{
string docType = tab_Iv.Rows[r]["DocType"].ToString();
decimal amt = SafeValue.SafeDecimal(tab_Iv.Rows[r]["Amt"], 0);
if (docType == "CN")
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) + amt).ToString("0.00");
}
else
{
rptRow["RevAmt"] = (SafeValue.SafeDecimal(rptRow["RevAmt"], 0) + amt).ToString("0.00");
}
}
decimal jobM3 = SafeValue.SafeDecimal(row["Volume"], 0);
decimal totM3 = SafeValue.SafeDecimal(row["TotVolume"], 0);
if (totM3 == 0 && jobM3 == 0)
{
jobM3 = 1;
totM3 = 1;
}
else if (totM3 == 0)
{
totM3 = 1;
}
string sql_Vo = string.Format("SELECT DocType,sum(LocAmt) as Amt FROM XAApPayable WHERE MastType='{0}' and (MastRefNo = '{1}') group by DocType", refType, refNo);
DataTable tab_Vo = ConnectSql.GetTab(sql_Vo);
for (int r = 0; r < tab_Vo.Rows.Count; r++)
{
string docType = tab_Vo.Rows[r]["DocType"].ToString();
decimal amt = SafeValue.SafeDecimal(tab_Vo.Rows[r]["Amt"], 0);
if (docType == "SC")
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) - SafeValue.ChinaRound(amt * jobM3 / totM3, 2)).ToString("0.00");
}
else
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) + SafeValue.ChinaRound(amt * jobM3 / totM3, 2)).ToString("0.00");
}
}
rptRow["ProfitAmt"] = (SafeValue.SafeDecimal(rptRow["RevAmt"], 0) - SafeValue.SafeDecimal(rptRow["CostAmt"], 0)).ToString("0.00");
rptRow["UserId"] = userName;
det.Rows.Add(rptRow);
}
}
catch { }
}
//EXPORT
sql = string.Format(@"SELECT ref.RefNo, ref.RefType, ref.AirportCode1 Port, ref.CurrencyRate,ref.FlightDate1 as Eta,ref.FlightDate0 as Etd, job.JobNo, job.CustomerId, job.HAWB, job.Weight, job.Volume,ref.Volume as TotVolume
FROM air_job AS job INNER JOIN air_ref AS ref ON job.RefNo = ref.RefNo
WHERE (ref.Etd >= '{0}') AND (ref.Etd < '{1}')", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"));
sql += " order by job.JobNo";
tab1 = ConnectSql.GetTab(sql);
for (int i = 0; i < tab1.Rows.Count; i++)
{
try
{
DataRow row = tab1.Rows[i];
string partyId = SafeValue.SafeString(row["CustomerId"], "");
string salesmanId = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("Select SalesmanId from XXParty where PartyId='{0}'", partyId)), "").ToUpper();
salesman = salesman.ToUpper();
if (salesman == "All" || salesman == "NA" || salesman == salesmanId)
{
DataRow rptRow = det.NewRow();
rptRow["PeriodStr"] = periodStr;
rptRow["ImpExpInd"] = "Export";
string refNo = row["RefNo"].ToString();
string jobNo = row["JobNo"].ToString();
string refType = row["JobType"].ToString().ToUpper();
if (refType == "CONSOL")
{
continue;
}
rptRow["RefType"] = refType;
rptRow["Salesman"] = salesmanId;
rptRow["CostAmt"] = 0;
rptRow["RevAmt"] = 0;
string sql_Iv = string.Format("SELECT DocType, sum(LocAmt) Amt FROM XAArInvoice WHERE MastType='{0}' and (MastRefNo = '{1}') and JobRefNo='{2}' group by DocType", refType, refNo, jobNo);
DataTable tab_Iv = ConnectSql.GetTab(sql_Iv);
for (int r = 0; r < tab_Iv.Rows.Count; r++)
{
string docType = tab_Iv.Rows[r]["DocType"].ToString();
decimal amt = SafeValue.SafeDecimal(tab_Iv.Rows[r]["Amt"], 0);
if (docType == "CN")
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) + amt).ToString("0.00");
}
else
{
rptRow["RevAmt"] = (SafeValue.SafeDecimal(rptRow["RevAmt"], 0) + amt).ToString("0.00");
}
}
decimal jobM3 = SafeValue.SafeDecimal(row["Volume"], 0);
decimal totM3 = SafeValue.SafeDecimal(row["TotVolume"], 0);
if (totM3 == 0 && jobM3 == 0)
{
jobM3 = 1;
totM3 = 1;
}
else if (totM3 == 0)
{
totM3 = 1;
}
string sql_Vo = string.Format("SELECT DocType,sum(LocAmt) as Amt FROM XAApPayable WHERE MastType='{0}' and (MastRefNo = '{1}') group by DocType", refType, refNo);
DataTable tab_Vo = ConnectSql.GetTab(sql_Vo);
for (int r = 0; r < tab_Vo.Rows.Count; r++)
{
string docType = tab_Vo.Rows[r]["DocType"].ToString();
decimal amt = SafeValue.SafeDecimal(tab_Vo.Rows[r]["Amt"], 0);
if (docType == "SC")
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) - SafeValue.ChinaRound(amt * jobM3 / totM3, 2)).ToString("0.00");
}
else
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) + SafeValue.ChinaRound(amt * jobM3 / totM3, 2)).ToString("0.00");
}
}
decimal exRate = SafeValue.SafeDecimal(row["ExRate"], 0);
if (exRate == 0)
{
exRate = 1;
}
string sql_detail = string.Format("SELECT Amount, Currency FROM SeaExportDetail where RefNo='{0}' and JobNo='{1}'", refNo, jobNo);
DataTable tab_detail = ConnectSql.GetTab(sql_detail);
for (int r = 0; r < tab_detail.Rows.Count; r++)
{
string cur = tab_detail.Rows[r]["Currency"].ToString();
decimal amt = SafeValue.SafeDecimal(tab_detail.Rows[r]["Amt"], 0);
if (cur == "SGD")
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) + amt).ToString("0.00");
}
else
{
rptRow["CostAmt"] = (SafeValue.SafeDecimal(rptRow["CostAmt"], 0) + amt * exRate).ToString("0.00");
}
}
rptRow["ProfitAmt"] = (SafeValue.SafeDecimal(rptRow["RevAmt"], 0) - SafeValue.SafeDecimal(rptRow["CostAmt"], 0)).ToString("0.00");
rptRow["UserId"] = userName;
det.Rows.Add(rptRow);
}
}
catch { }
}
DataSetHelper help = new DataSetHelper();
DataTable rptTab = help.SelectGroupByInto("rptTab", det, "PeriodStr,Salesman,ImpExpInd,RefType,count(Salesman) Cnt,sum(RevAmt) RevAmt,sum(CostAmt) CostAmt,sum(ProfitAmt) ProfitAmt", "",
"PeriodStr,Salesman,ImpExpInd,RefType");
return(rptTab);
}
protected void grid_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string s = e.Parameters;
if (s == "Save")
{
ASPxButtonEdit btn_BankCode = grid.FindEditFormTemplateControl("btn_BankCode") as ASPxButtonEdit;
string bankCode = SafeValue.SafeString(btn_BankCode.Text);
ASPxSpinEdit spin_LcAmount = grid.FindEditFormTemplateControl("spin_LcAmount") as ASPxSpinEdit;
decimal lcAmount = SafeValue.SafeDecimal(spin_LcAmount.Value, 1);
ASPxTextBox txt_Id = this.grid.FindEditFormTemplateControl("txt_Id") as ASPxTextBox;
string id = SafeValue.SafeString(txt_Id.Text);
if (VilaLC(bankCode, lcAmount, id) && lcAmount != 0)
{
e.Result = "Fail!No Enough Credit Limit";
return;
}
else
{
e.Result = SaveLc();
ASPxComboBox cmb_Type = grid.FindEditFormTemplateControl("cmb_Type") as ASPxComboBox;
ASPxTextBox txt_LcNo = this.grid.FindEditFormTemplateControl("txt_LcNo") as ASPxTextBox;
string type = SafeValue.SafeString(cmb_Type.Text);
bool isAddLog = false;
if (cmb_Type.Text == SafeValue.SafeString(ConnectSql.ExecuteScalar("Select LcType from Ref_LC where LcNo='" + txt_LcNo.Text + "'")))
{
}
else
{
isAddLog = true;
}
if (isAddLog)
{
if (type == "IMPORT LC")
{
EzshipLog.Activity(txt_LcNo.Text, "", "IMPORT LC", "RECEIVING");
}
if (type == "EXPORT LC")
{
EzshipLog.Activity(txt_LcNo.Text, "", "EXPORT LC", "ISSUING");
}
if (type == "STANDBY LC")
{
EzshipLog.Activity(txt_LcNo.Text, "", "EXPORT LC", "COLLECTION");
}
if (type == "BANK GUARANTEE")
{
EzshipLog.Activity(txt_LcNo.Text, "", "BANK GUARANTEE", "REMARK");
}
if (type == "SHIPPING GUARANTEE")
{
EzshipLog.Activity(txt_LcNo.Text, "", "SHIPPING GUARANTEE", "REMARK");
}
if (type == "OTHERS")
{
EzshipLog.Activity(txt_LcNo.Text, "", "OTHERS", "OTHERS");
}
}
}
}
}
protected void btn_search_Click(object sender, EventArgs e)
{
string docType = this.cbo_DocType.Text;
string refNo = this.txt_RefNo.Text;
string contno = this.txt_ContNo.Text;
DateTime date1 = this.txt_from.Date; //.ToString("yyyy-MM-dd");
DateTime date2 = this.txt_end.Date; //.ToString("yyyy-MM-dd");
string From = date1.Date.ToString("yyyyMMdd");
string To = date2.Date.ToString("yyyyMMdd");
string name = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
string sql_p = string.Format(@"select PartyId from xxparty where Name='{0}'", name);
string haulierId = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql_p));
//string ClientName = info["ClientName"].ToString();
List <ConnectEdi.cmdParameters> list = new List <ConnectEdi.cmdParameters>();
list.Add(new ConnectEdi.cmdParameters("@ClientRefNo", refNo, SqlDbType.NVarChar, 8));
list.Add(new ConnectEdi.cmdParameters("@DateFrom", From, SqlDbType.NVarChar, 8));
list.Add(new ConnectEdi.cmdParameters("@DateTo", To, SqlDbType.NVarChar, 8));
list.Add(new ConnectEdi.cmdParameters("@JobType", docType, SqlDbType.NVarChar, 8));
list.Add(new ConnectEdi.cmdParameters("@ContNo", contno, SqlDbType.NVarChar, 8));
list.Add(new ConnectEdi.cmdParameters("@HaulierId", haulierId, SqlDbType.NVarChar, 8));
string sql = string.Format(@"select job.Id,det1.Id as ContId,job.JobNo,job.StatusCode as JobStatus,job.JobDate,det1.YardAddress as Depot,job.ClientRefNo,
job.PermitNo,job.Remark,job.SpecialInstruction,det1.ContainerNo,det1.SealNo,det1.ContainerType,job.EtaDate,job.EtaTime,job.EtdDate,
det1.UrgentInd,job.OperatorCode,job.CarrierBkgNo,det1.oogInd,det1.CfsStatus,det1.DischargeCell,
job.Pol,job.Pod,job.Vessel,job.Voyage,job.PickupFrom,job.DeliveryTo,det1.ScheduleDate,det1.StatusCode ,det1.F5Ind,det1.EmailInd,det1.WarehouseStatus,
(select top 1 Code from XXParty where PartyId=job.ClientId) as client,(select top 1 code from XXParty where PartyId=job.HaulierId) as Haulier,
job.Terminalcode,job.JobType,det1.PortnetStatus,det1.Permit,
(select '<span class='''+det2.Statuscode+'''>'+TripCode+'</span>' from CTM_JobDet2 as det2 where Det1Id=det1.Id for xml path('')) as trips,
datediff(hour,isnull((select top 1 convert(nvarchar(8),det2.FromDate,112)+' '+det2.FromTime
from ctm_jobdet2 as det2 where det2.Det1Id=det1.Id and (det2.Statuscode='S' or det2.Statuscode='C') order by det2.FromDate,det2.FromTime),
convert(nvarchar(8),getdate(),112)+' '+convert(nvarchar(5),getdate(),114)),
case when det1.StatusCode='Completed' then isnull((select top 1 convert(nvarchar(8),det2.ToDate,112)+' '+det2.ToTime
from ctm_jobdet2 as det2 where det2.Det1Id=det1.Id and det2.Statuscode='C' order by det2.ToDate desc,det2.ToTime desc),
convert(nvarchar(8),getdate(),112)+' '+convert(nvarchar(5),getdate(),114)) else getdate() end) as hr,
case job.JobType when 'IMP' then (case det1.StatusCode when 'New' then 'IMP' when 'InTransit' then 'RET' else '' end)
when 'EXP' then (case det1.StatusCode when 'New' then 'COL' when 'InTransit' then 'EXP' else '' end) else '' end as NextTrip,
isnull((select ','+det2.Statuscode+':'+TripCode from CTM_JobDet2 as det2 where Det1Id=det1.Id for xml path('')),'')+',' as str_trips
from CTM_Job as job left outer join CTM_JobDet1 as det1 on job.JobNo=det1.JobNo");
string sql_where = " and job.JobStatus='Confirmed'";
if (refNo.Length > 0)
{
sql_where = GetWhere(sql_where, "job.ClientRefNo='" + refNo + "'");
}
if (contno.Length > 0)
{
sql_where = GetWhere(sql_where, "det1.ContainerNo='" + contno + "'");
}
if (docType.Length > 0)
{
sql_where = GetWhere(sql_where, "job.JobType='" + docType + "'");
}
if (haulierId.Length > 0)
{
sql_where = GetWhere(sql_where, "job.HaulierId='" + haulierId + "'");
}
if (sql_where.Equals(""))
{
sql_where = string.Format(@" datediff(d,'{0}',det1.Eta)>=0 and datediff(d,'{1}',det1.Eta)<=0", From, To);
sql += " where job.JobType in ('IMP','EXP','LOC') and IsTrucking='Yes' and " + sql_where;
}
else
{
sql += " where Contractor='YES'" + sql_where;
sql += " order by job.EtaDate,job.JobNo,det1.Id,det1.StatusCode desc,job.JobDate asc";
}
DataTable dt = ConnectEdi.GetDataTable(sql);
//throw new Exception(sql.ToString());
int n = dt.Rows.Count;
this.grd.DataSource = dt;
this.grd.DataBind();
}
public string ShowRefNo(object refNo)
{
string sql = string.Format(@"select JobNo from ctm_job where ClientRefNo='{0}'", refNo);
return(SafeValue.SafeString(ConnectSql.ExecuteScalar(sql)));
}
protected int TransferProduct(string sku, string lotNo, string des, string wh, int qty, string loc, string doNo, int tId, string toWh, string toLoc)
{
int pId = 0;
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhTransfer), "Id='" + tId + "'");
WhTransfer transfer = C2.Manager.ORManager.GetObject(query) as WhTransfer;
bool action = false;
string transferNo = "";
if (transfer == null)
{
action = true;
transfer = new WhTransfer();
transferNo = C2Setup.GetNextNo("Transfer");
}
string userId = HttpContext.Current.User.Identity.Name;
transfer.TransferNo = transferNo;
transfer.RequestPerson = userId;
transfer.RequestDate = DateTime.Now;
transfer.TransferDate = DateTime.Now;
transfer.Pic = "";
transfer.ConfirmPerson = "";
transfer.ConfirmDate = DateTime.Now;
transfer.StatusCode = "CLS";
if (action)
{
transfer.TransferNo = transferNo;
transfer.CreateBy = HttpContext.Current.User.Identity.Name;
transfer.CreateDateTime = DateTime.Now;
C2Setup.SetNextNo("Transfer", transferNo);
Manager.ORManager.StartTracking(transfer, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(transfer);
pId = transfer.Id;
}
int id = 0;
Wilson.ORMapper.OPathQuery query1 = new Wilson.ORMapper.OPathQuery(typeof(WhTransferDet), "Id='" + id + "'");
WhTransferDet det = C2.Manager.ORManager.GetObject(query1) as WhTransferDet;
if (det == null)
{
det = new WhTransferDet();
}
det.TransferNo = transferNo;
det.Product = sku;
det.LotNo = lotNo;
det.Qty1 = qty;
det.FromWarehouseId = wh;
det.FromLocId = loc;
det.ToWarehouseId = toWh;
det.ToLocId = toLoc;
det.Des1 = des;
det.CreateBy = userId;
det.CreateDateTime = DateTime.Now;
det.UpdateBy = userId;
det.UpdateDateTime = DateTime.Now;
Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(det);
id = det.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 DoNo, 'OUT' as DoType,Product
,'{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,'{5}' as Des1,'' as Packing,'{6}' as Location,'Delivered' as ProcessStatus
from wh_dodet2 det where DoType='IN' and DoNo='{0}' and Product='{1}' and LotNo='{3}' and Location='{6}' select @@identity", doNo, sku, qty, lotNo, EzshipHelper.GetUserName(), des, loc);
int doOutId = 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)";
sql += string.Format(@"select DoNo, 'IN' as DoType,Product
,'{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,'{5}' as Des1,'' as Packing,'{6}' as Location,'Delivered' as ProcessStatus
from wh_dodet2 det where DoType='IN' and DoNo='{0}' and Product='{1}' and LotNo='{3}' and Location='{7}' select @@identity", doNo, sku, qty, lotNo, EzshipHelper.GetUserName(), des, toLoc, wh);
int doInId = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
sql = string.Format(@"update wh_TransferDet set DoInId='{1}',DoOutId='{2}' where Id={0}", id, doInId, doOutId);
C2.Manager.ORManager.ExecuteCommand(sql);
return(pId);
}
private void OnLoad()
{
int start = 0;
int end = 5000;
string doNo = SafeValue.SafeString(Request.QueryString["Sn"].ToString());
decimal qty5 = 0;
decimal totalQty = 0;
string produt = "";
decimal value = 0;
decimal value1 = 0;
decimal bQty = 0;
decimal qty = 0;
decimal pendingPickQty = 0;
for (int i = start; i < end; i++)
{
ASPxCheckBox isPay = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Id"], "ack_IsPay") as ASPxCheckBox;
ASPxTextBox id = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Id"], "txt_Id") as ASPxTextBox;
ASPxTextBox sku = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["ProductCode"], "txt_Product") as ASPxTextBox;
ASPxTextBox lotNo = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["LotNo"], "txt_LotNo") as ASPxTextBox;
ASPxSpinEdit spin_Qty = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Qty"], "spin_Qty") as ASPxSpinEdit;
ASPxLabel spin_Picked = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Qty5"], "spin_Picked") as ASPxLabel;
ASPxLabel balQty = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["AvaibleQty"], "spin_AvaibleQty") as ASPxLabel;
ASPxLabel location = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Location"], "lbl_Location") as ASPxLabel;
ASPxLabel price = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Price"], "spin_Price") as ASPxLabel;
ASPxLabel spin_PengingPickQty = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["PengingPickQty"], "spin_PengingPickQty") as ASPxLabel;
ASPxLabel txt_Remark = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["Remark"], "txt_Remark") as ASPxLabel;
ASPxLabel txt_PalletNo = this.ASPxGridView1.FindRowCellTemplateControl(i, (DevExpress.Web.ASPxGridView.GridViewDataColumn) this.ASPxGridView1.Columns["PalletNo"], "txt_PalletNo") as ASPxLabel;
if (sku != null)
{
string sql = string.Format(@"select sum(Qty5) as Qty5 from wh_dodet where ProductCode='{0}' and DoNo='{1}' and JobStatus='Pending' and Remark='{2}' and PalletNo='{3}'", SafeValue.SafeString(sku.Text), doNo, SafeValue.SafeString(txt_Remark.Text), SafeValue.SafeString(txt_PalletNo.Text));
qty5 = SafeValue.SafeDecimal(ConnectSql.ExecuteScalar(sql));
sql = string.Format(" select sum(Qty1) as Qty from wh_dodet2 det inner join Wh_DO mast on det.DoNo=mast.DoNo where Product='{0}' and det.DoType='IN' and det.Remark='{1}'and PalletNo='{2}'", SafeValue.SafeString(sku.Text), SafeValue.SafeString(txt_Remark.Text), SafeValue.SafeString(txt_PalletNo.Text));
totalQty = SafeValue.SafeDecimal(ConnectSql.ExecuteScalar(sql));
decimal picking = SafeValue.SafeDecimal(spin_Qty.Text);
bQty = SafeValue.SafeInt(balQty.Text, 0);
sql = string.Format(@"select Qty5 from wh_dodet where Id={0}", SafeValue.SafeInt(id.Text, 0));
decimal qty5_n = SafeValue.SafeDecimal(ConnectSql.ExecuteScalar(sql));
sql = string.Format(@"select Count(*) from wh_dodet2 where Product='{0}' and LotNo='{1}' and DoNo='{2}' and Remark='{3}' and PalletNo='{4}'", SafeValue.SafeString(sku.Text), SafeValue.SafeString(lotNo.Text), doNo, SafeValue.SafeString(txt_Remark.Text), SafeValue.SafeString(txt_PalletNo.Text));
decimal cnt = SafeValue.SafeDecimal(ConnectSql.ExecuteScalar(sql), 0);
sql = string.Format(@"select Qty1 from wh_dodet2 where Product='{0}' and LotNo='{1}' and DoNo='{2}' and Remark='{3}' and PalletNo='{4}'", SafeValue.SafeString(sku.Text), SafeValue.SafeString(lotNo.Text), doNo, SafeValue.SafeString(txt_Remark.Text), SafeValue.SafeString(txt_PalletNo.Text));
decimal qty1 = SafeValue.SafeDecimal(ConnectSql.ExecuteScalar(sql));
if (cnt == 0 && qty1 < bQty)
{
if (totalQty >= qty5)
{
if (sku.Text != produt)
{
value = 0;
value1 = 0;
qty = 0;
if (bQty < qty5)
{
value = bQty;
}
else
{
value = qty5;
}
isPay.Checked = true;
spin_Picked.Text = value.ToString();
}
if (sku.Text == produt && qty5 > qty)
{
if ((bQty + qty) < qty5)
{
value = bQty;
}
else
{
value = qty5 - value1;
}
isPay.Checked = true;
spin_Picked.Text = value.ToString();
}
//if (qty5 == qty)
//{
// spin_Picked.Text = "";
// isPay.Checked = false;
//}
if (pendingPickQty == SafeValue.SafeInt(spin_PengingPickQty.Text, 0))
{
spin_PengingPickQty.Text = "";
}
}
pendingPickQty += SafeValue.SafeInt(spin_PengingPickQty.Text, 0);
}
}
if (sku != null && isPay != null && isPay.Checked)
{
list.Add(new Record(SafeValue.SafeInt(id.Text, 0), sku.Text, lotNo.Text, SafeValue.SafeInt(spin_Qty.Value, 0), SafeValue.SafeInt(balQty.Text, 0), location.Text, SafeValue.SafeDecimal(price.Text, 0)
, SafeValue.SafeInt(spin_PengingPickQty.Text, 0), txt_Remark.Text, txt_PalletNo.Text));
produt = sku.Text;
spin_Qty.Text = value.ToString();
value = qty5 - value - value1;
value1 += bQty;
qty += SafeValue.SafeInt(spin_Qty.Text, 0);
}
else if (sku == null)
{
break;
}
;
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
string doNo = SafeValue.SafeString(Request.QueryString["Sn"].ToString());
string type = SafeValue.SafeString(Request.QueryString["type"].ToString());
string s = e.Parameters;
bool action = false;
decimal totalQty = 0;
decimal value = 0;
string product = "";
int result = 0;
if (Request.QueryString["Sn"] != null)
{
#region Vali
if (s == "Vali")
{
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sku = list[i].sku;
string sql = string.Format(@"select count(*) from Wh_DoDet2 d1 inner join Wh_DoDet d on d.ProductCode=d1.Product and d.DoType=d1.DoType and d1.DoNo=d.DoNo where d1.DoNo='{0}' and d1.Product='{1}'", doNo, sku);
int cnt = SafeValue.SafeInt(Manager.ORManager.ExecuteScalar(sql), 0);
if (cnt > 0)
{
e.Result = "Fail!Had Picked";
return;
}
}
try
{
for (int i = 0; i < list.Count; i++)
{
string sku = list[i].sku;
decimal balqty = list[i].avaibleQty;
decimal qty = list[i].qty;
decimal picking = list[i].pickQty;
if (qty <= 0)
{
e.Result = "No Qty";
return;
}
if (qty > balqty)
{
e.Result = "No Balance";
return;
}
if (balqty < (qty + picking))
{
e.Result = "Had Picking,No Enough Stock";
return;
}
//if(picking==balqty){
// e.Result = "Had Picking,Can't Pick again";
// return;
//}
}
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sku = list[i].sku;
decimal qty = list[i].qty;
string lotNo = list[i].lotNo;
decimal balqty = list[i].avaibleQty;
string loc = list[i].loc;
decimal price = list[i].price;
decimal pendingpick = list[i].pickQty;
string remark = list[i].remark;
string palletNo = list[i].palletNo;
for (int j = 0; j < list.Count; j++)
{
string sku1 = list[j].sku;
decimal qty1 = list[j].qty;
if (sku1 == sku)
{
totalQty = qty1 + value;
}
value = qty1;
}
string sql_sum = string.Format(@"select sum(Qty1) as TotalQty from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, lotNo, remark, palletNo);
int total = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_sum), 0);
string sql = string.Format(@"select count(*) from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and LotNo='{2}' and Location='{3}' and Remark='{4}' and PalletNo='{5}'", doNo, sku, lotNo, loc, remark, palletNo);
int cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
string sql_totalQty5 = string.Format(@"select sum(Qty5) as PickQty from Wh_DoDet where DoNo='{0}' and ProductCode='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
string sql_totalQty1 = string.Format(@"select sum(Qty1) as PickQty from Wh_DoDet where DoNo='{0}' and ProductCode='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
int total_qty5 = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_totalQty5), 0);
int total_qty1 = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_totalQty1), 0);
if (cnt > 0 && (total < total_qty1 || total < total_qty5))
{
sql = string.Format(@"update Wh_DoDet2 set Qty1=Qty1+{3} where DoNo='{0}' and Product='{1}' and LotNo='{2}' and Remark='{3}' and PalletNo='{4}'", doNo, sku, lotNo, qty, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
action = false;
}
else if (cnt > 0 && (total_qty1 == total || total_qty5 == total))
{
sql = string.Format(@"delete from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and LotNo='{2}' and Remark='{3}' and PalletNo='{4}'", doNo, sku, lotNo, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
action = true;
}
else if (cnt == 0)
{
if ((total_qty1 == total || total_qty5 == total) && totalQty < total)
{
sql = string.Format(@"delete from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Qty1={2} and Remark='{3}' and PalletNo='{4}'", doNo, sku, qty, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
}
if ((total_qty1 == total || total_qty5 == total) && totalQty >= total)
{
sql = string.Format(@"delete from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
}
action = true;
}
if (action)
{
TransferToDo(id, doNo, sku, lotNo, qty, loc, price);
}
sql = string.Format(@"select JobStatus from Wh_DoDet where Id={0}", id);
string jobstatus = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
if (jobstatus == "Pending")
{
sql = string.Format(@"update Wh_DoDet set Qty1=Qty5,Qty5=0,JobStatus='Picked' where Id={0}", id);
result = SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
}
}
EzshipLog.Log(doNo, "", "OUT", "Picked");
e.Result = "Success";
}
catch { }
}
#endregion
#region OK
if (s == "OK")
{
try
{
for (int i = 0; i < list.Count; i++)
{
string sku = list[i].sku;
decimal balqty = list[i].avaibleQty;
decimal qty = list[i].qty;
decimal picking = list[i].pickQty;
if (qty <= 0)
{
e.Result = "No Qty";
return;
}
if (qty > balqty)
{
e.Result = "No Balance";
return;
}
if (balqty < (qty + picking))
{
e.Result = "Had Picking,No Enough Stock";
return;
}
//if (picking >balqty)
//{
// e.Result = "Had Picking,Can't Pick again";
// return;
//}
}
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sku = list[i].sku;
decimal qty = list[i].qty;
string lotNo = list[i].lotNo;
decimal balqty = list[i].avaibleQty;
string loc = list[i].loc;
decimal price = list[i].price;
string remark = list[i].remark;
string palletNo = list[i].palletNo;
for (int j = 0; j < list.Count; j++)
{
string sku1 = list[j].sku;
decimal qty1 = list[j].qty;
if (sku1 == sku)
{
totalQty = qty1 + value;
}
value = qty1;
}
string sql_sum = string.Format(@"select sum(Qty1) as TotalQty from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
int total = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_sum), 0);
string sql = string.Format(@"select count(*) from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and LotNo='{2}' and Location='{3}' and Remark='{4}' and PalletNo='{5}'", doNo, sku, lotNo, loc, remark, palletNo);
int cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
string sql_totalQty5 = string.Format(@"select sum(Qty5) as PickQty from Wh_DoDet where DoNo='{0}' and ProductCode='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
string sql_totalQty1 = string.Format(@"select sum(Qty1) as PickQty from Wh_DoDet where DoNo='{0}' and ProductCode='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
int total_qty5 = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_totalQty5), 0);
int total_qty1 = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_totalQty1), 0);
if (cnt > 0 && (total < total_qty1 || total < total_qty5))
{
sql = string.Format(@"update Wh_DoDet2 set Qty1=Qty1+{3} where DoNo='{0}' and Product='{1}' and LotNo='{2}' and Remark='{3}' and PalletNo='{4}'", doNo, sku, lotNo, qty, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
action = false;
}
else if (cnt > 0 && (total_qty1 == total || total_qty5 == total))
{
sql = string.Format(@"delete from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
action = true;
}
else if (cnt == 0)
{
if ((total_qty1 == total || total_qty5 == total) && totalQty < total)
{
sql = string.Format(@"select count(*) from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Qty1={2} and Remark='{3}' and PalletNo='{4}'", doNo, sku, qty, remark, palletNo);
cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (cnt > 0)
{
sql = string.Format(@"delete from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Qty1={2} and Remark='{3}' and PalletNo='{4}'", doNo, sku, qty, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
}
}
if ((total_qty1 == total || total_qty5 == total) && totalQty >= total)
{
sql = string.Format(@"delete from Wh_DoDet2 where DoNo='{0}' and Product='{1}' and Remark='{2}' and PalletNo='{3}'", doNo, sku, remark, palletNo);
C2.Manager.ORManager.ExecuteCommand(sql);
}
action = true;
}
if (action)
{
TransferToDo(id, doNo, sku, lotNo, qty, loc, price);
}
sql = string.Format(@"select JobStatus from Wh_DoDet where Id={0}", id);
string jobstatus = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
if (jobstatus == "Pending")
{
sql = string.Format(@"update Wh_DoDet set Qty1=Qty5+Qty1,JobStatus='Picked' where Id={0}", id);
result = SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
sql = string.Format(@"update Wh_DoDet set Qty5=0,JobStatus='Picked' where Id={0}", id);
result = SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
}
product = sku;
}
e.Result = "Success";
EzshipLog.Log(doNo, "", "OUT", "Picked");
}
catch { }
}
#endregion
}
else
{
e.Result = "Please keyin select party ";
}
}
protected void btn_CreateInv_Click(object sender, EventArgs e)
{
string invNo = "";
string mastType = "WH";
DateTime dt = DateTime.Today;
bool isNew = false;
int docId = 0;
if (list.Count > 0)
{
XAArInvoice iv = null;
for (int i = 0; i < list.Count; i++)
{
string counterType = "AR-IV";
string partyId = list[i].partyId;
string product = list[i].code;
string doNo = list[i].doNo;
string des = list[i].des;
string lotNo = list[i].lotNo;
decimal qty = list[i].qty;
decimal price = list[i].price;
int days = list[i].days;
decimal surcharge = list[i].surcharge;
//string sql = string.Format(@"select DocNo from XAArInvoice where MastRefNo='{0}' and InvType='STORAGE'", doNo);
//invNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql));
//if (invNo.Length==0)
//{
// iv = new XAArInvoice();
// invNo = C2Setup.GetNextNo("", counterType, dt);
// isNew = true;
//}
//else
//{
// Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(XAArInvoice), "DocNo='" + invNo + "'");
// iv = C2.Manager.ORManager.GetObject(query) as XAArInvoice;
// isNew = false;
//}
iv = new XAArInvoice();
invNo = C2Setup.GetNextNo("", counterType, dt);
isNew = true;
iv.DocType = "IV";
iv.DocDate = dt;
iv.DocNo = invNo;
iv.PartyTo = partyId;
iv.CurrencyId = System.Configuration.ConfigurationManager.AppSettings["Currency"];
iv.ExRate = 1;
iv.AcCode = EzshipHelper.GetAccApCode(iv.PartyTo, iv.CurrencyId);
iv.AcSource = "DB";
iv.Description = "";
iv.Term = "CASH";
string[] currentPeriod = EzshipHelper.GetAccPeriod(iv.DocDate);
iv.AcYear = SafeValue.SafeInt(currentPeriod[1], iv.DocDate.Year);
iv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], iv.DocDate.Month);
iv.MastRefNo = doNo;
iv.JobRefNo = "";
iv.MastType = mastType;
iv.DocAmt = 0;
iv.LocAmt = 0;
iv.BalanceAmt = 0;
iv.CancelDate = new DateTime(1900, 1, 1);
iv.CancelInd = "N";
iv.DocDueDate = dt;
iv.ExportInd = "N";
iv.SpecialNote = "";
iv.UserId = EzshipHelper.GetUserName();
iv.EntryDate = DateTime.Now;
iv.InvType = "STORAGE";
//if (isNew)
//{
C2.Manager.ORManager.StartTracking(iv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(iv);
C2Setup.SetNextNo(iv.DocType, counterType, invNo, iv.DocDate);
//}
//else
//{
Manager.ORManager.StartTracking(iv, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(iv);
//}
try
{
C2.XAArInvoiceDet det = new C2.XAArInvoiceDet();
det.DocId = iv.SequenceId;
det.DocLineNo = i + 1;
det.DocNo = invNo;
det.DocType = "IV";
det.AcCode = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("select ArCode from ref_product where Code='{0}'", product)), System.Configuration.ConfigurationManager.AppSettings["ItemArCode"]);
det.AcSource = "CR";
det.MastRefNo = doNo;
det.JobRefNo = lotNo;
det.MastType = mastType;
det.SplitType = "";
//sql = string.Format(@"select * from XXChgCode where ChgcodeId='{0}'", product);
//DataTable tab_chg = ConnectSql.GetTab(sql);
//for (int j = 0; j < tab_chg.Rows.Count; j++)
//{
// det.Gst = SafeValue.SafeDecimal(tab_chg.Rows[j]["GstP"]);
//}
det.ChgCode = product;
det.ChgDes1 = des;
det.ChgDes2 = "";
det.ChgDes3 = "";
det.Price = price;
det.Qty = qty;
det.Unit = "";
det.Currency = iv.CurrencyId;
det.ExRate = 1;
det.Gst = 0;
if (det.ExRate == 0)
{
det.ExRate = 1;
}
if (det.Gst > 0)
{
det.GstType = "S";
}
else if (det.Currency == System.Configuration.ConfigurationManager.AppSettings["Currency"])
{
det.GstType = "E";
}
else
{
det.GstType = "Z";
}
decimal amt = SafeValue.ChinaRound(det.Qty * det.Price, 2);//*days
decimal gstAmt = SafeValue.ChinaRound((amt * det.Gst), 2);
decimal docAmt = amt + gstAmt;
decimal locAmt = SafeValue.ChinaRound(docAmt * det.ExRate, 2);
decimal surchageAmt = SafeValue.ChinaRound(surcharge * qty, 2);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
det.OtherAmt = surchageAmt;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
if (surcharge != 0)
{
det.ChgCode = "STORE";
det.Qty = 1;
det.Price = surcharge;
string sql = string.Format(@"select * from XXChgCode where ChgcodeId='{0}'", det.ChgCode);
DataTable tab = ConnectSql.GetTab(sql);
for (int j = 0; j < tab.Rows.Count; j++)
{
det.AcCode = SafeValue.SafeString(tab.Rows[j]["ArCode"]);
det.ChgDes1 = SafeValue.SafeString(tab.Rows[j]["ChgcodeDes"]);
det.GstType = SafeValue.SafeString(tab.Rows[j]["GstTypeId"]);
det.Gst = SafeValue.SafeDecimal(tab.Rows[j]["GstP"]);
}
amt = SafeValue.ChinaRound(det.Qty * det.Price, 2);
gstAmt = SafeValue.ChinaRound((amt * det.Gst), 2);
docAmt = amt + gstAmt;
locAmt = SafeValue.ChinaRound(docAmt * det.ExRate, 2);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
docId = iv.SequenceId;
}
catch
{
}
UpdateMaster(docId);
}
Response.Redirect("/opsAccount/ArInvoiceEdit.aspx?no=" + invNo);
}
}
public string FilePath(int id)
{
string sql = string.Format("select top 1 FilePath from CTM_Attachment where JobNo='{0}'", id);
return(SafeValue.SafeString(ConnectSql.ExecuteScalar(sql)));
}
protected void ASPxGridView1_CustomDataCallback1(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
ASPxTextBox Id = ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
string s = e.Parameters;
if (s == "Confirm")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Confirm' where StatusCode='Draft'");
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "UnConfirm")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Draft' where Id={0}", SafeValue.SafeInt(Id.Text, 0));
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "UnCancel")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Draft' where Id={0}", SafeValue.SafeInt(Id.Text, 0));
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "Payroll")
{
string sql = string.Format(@"select Person,sum(Amt) as TotalAmt,max(Remark) as Remark from Hr_Quote group by Person");
DataTable tab = ConnectSql.GetTab(sql);
int fromYear = txt_from.Date.Year;
int fromMonth = txt_from.Date.Month;
string name = HttpContext.Current.User.Identity.Name;
int toYear = txt_end.Date.Year;
int toMonth = txt_end.Date.Month;
bool result = false;
string from = txt_from.Date.ToString("yyyy-MM-dd");
string to = txt_end.Date.ToString("yyyy-MM-dd");
int month = toMonth - fromMonth;
DateTime firstDayOfFromMonth = new DateTime(fromYear, fromMonth, 1);
DateTime lastDayOfFromMonth = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
DateTime firstDayOfToMonth = new DateTime(toYear, toMonth, 1);
DateTime lastDayOfToMonth = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));
if (month < 2)
{
for (int a = 0; a <= month; a++)
{
for (int i = 0; i < tab.Rows.Count; i++)
{
int person = SafeValue.SafeInt(tab.Rows[i]["Person"], 0);
string sql_pay = string.Format(@"select count(*) from Hr_Payroll where Person={0} and (FromDate>='{1}' and ToDate<='{2}')", person, firstDayOfFromMonth.ToString("yyyy-MM-dd"), lastDayOfFromMonth.ToString("yyyy-MM-dd"));
int cnt = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_pay), 0);
string sql_pay_to = string.Format(@"select count(*) from Hr_Payroll where Person={0} and (FromDate>='{1}' and ToDate<='{2}')", person, firstDayOfToMonth.ToString("yyyy-MM-dd"), lastDayOfToMonth.ToString("yyyy-MM-dd"));
int cnt_to = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_pay_to), 0);
if (cnt == 0)
{
#region From Date
decimal amt = SafeValue.SafeDecimal(tab.Rows[i]["TotalAmt"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
HrPayroll payroll = new HrPayroll();
payroll.Person = person;
payroll.FromDate = firstDayOfFromMonth;
payroll.ToDate = lastDayOfFromMonth;
payroll.StatusCode = "Draft";
payroll.Term = "";
payroll.Remark = "";
payroll.Pic = "";
payroll.CreateBy = name;
payroll.CreateDateTime = DateTime.Now;
payroll.Amt = amt;
Manager.ORManager.StartTracking(payroll, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(payroll);
sql = string.Format(@"select * from Hr_Quote where Person={0}", person);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = SafeValue.SafeString(tabDet.Rows[j]["PayItem"]);
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})", payroll.Id, code, des, payamt, name);
ConnectSql.ExecuteSql(sql);
}
#endregion
result = true;
}
else if (cnt_to == 0)
{
#region To Date
decimal amt = SafeValue.SafeDecimal(tab.Rows[i]["TotalAmt"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
HrPayroll payroll = new HrPayroll();
payroll.Person = person;
payroll.FromDate = firstDayOfToMonth;
payroll.ToDate = lastDayOfToMonth;
payroll.StatusCode = "Draft";
payroll.Term = "";
payroll.Remark = "";
payroll.Pic = "";
payroll.CreateBy = name;
payroll.CreateDateTime = DateTime.Now;
payroll.Amt = amt;
Manager.ORManager.StartTracking(payroll, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(payroll);
sql = string.Format(@"select * from Hr_Quote where Person={0}", person);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = SafeValue.SafeString(tabDet.Rows[j]["PayItem"]);
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})", payroll.Id, code, des, payamt, name);
ConnectSql.ExecuteSql(sql);
}
#endregion
result = true;
}
else
{
result = false;
e.Result = "Had Created Payroll!No Need Again!";
}
}
}
if (result)
{
e.Result = "Success!";
}
}
}
}
public void ImportJob(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
string bkgRefNo = ws.Cells["C" + 2].StringValue;
string jobOrder = ws.Cells["F" + 3].StringValue;
string conNo = ws.Cells["J" + 2].StringValue;
string shipper = ws.Cells["C" + 3].StringValue;
string consignee = ws.Cells["C" + 4].StringValue;
string responsible = ws.Cells["L" + 4].StringValue;
string ic = ws.Cells["D" + 5].StringValue;
string uen = ws.Cells["I" + 5].StringValue;
string email1 = ws.Cells["D" + 6].StringValue;
string email2 = ws.Cells["H" + 6].StringValue;
string tel1 = ws.Cells["D" + 7].StringValue;
string tel2 = ws.Cells["F" + 7].StringValue;
string mobile1 = ws.Cells["H" + 7].StringValue;
string mobile2 = ws.Cells["L" + 7].StringValue;
string address = ws.Cells["C" + 8].StringValue;
string receiver = ws.Cells["C" + 10].StringValue;
string contact = ws.Cells["C" + 11].StringValue;
string delivery = ws.Cells["C" + 12].StringValue;
string payable = ws.Cells["C" + 13].StringValue;
string prepaidInd = ws.Cells["D" + 13].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 13].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["M" + 13].StringValue);
string dutyPayment = ws.Cells["C" + 14].StringValue;
string incoterm = ws.Cells["C" + 15].StringValue;
decimal miscFee = SafeValue.SafeDecimal(ws.Cells["E" + 15].StringValue);
string remark = ws.Cells["C" + 16].StringValue;
string fee1CurrId = ws.Cells["M" + 19].StringValue;
string totalQty = ws.Cells["I" + 19].StringValue;
string packType = ws.Cells["J" + 19].StringValue;
string weight = ws.Cells["K" + 19].StringValue;
string m3 = ws.Cells["L" + 19].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", 0, SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", shipper, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile2", mobile2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Desc1", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", miscFee, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark2", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeInt(totalQty, 0), SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "E", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,JobNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,Email1,Email2,Tel1,Tel2,Mobile1,Mobile2,Desc1,ClientId,ClientEmail,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Remark2,Currency,Qty,PackTypeOrig,Weight,Volume)
values (@BookingNo,@JobNo,@JobType,@ContNo,'ORDER',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@Email1,@Email2,@Tel1,@Tel2,@Mobile1,@Mobile2,@Desc1,@ClientId,@ClientEmail,@Remark1,@PrepaidInd,@CollectAmount1,@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Remark2,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
string sql_id = string.Format("select top 1 Id from job_house order by Id desc");
string Oid = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql_id));
insert_cargo(SafeValue.SafeInt(Oid, 0));
if (Oid.Length > 0)
{
action = true;
for (int i = 17; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
string H = ws.Cells["H" + i].StringValue;
string I = ws.Cells["I" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", Oid, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", Oid);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(F, 0), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeInt(F, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@H", SafeValue.SafeDecimal(H), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", Oid, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@C,@D,@E,@F,@G,@H,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
protected string SaveLc()
{
try
{
ASPxTextBox txt_LcNo = grid.FindEditFormTemplateControl("txt_LcNo") as ASPxTextBox;
ASPxTextBox txt_Id = grid.FindEditFormTemplateControl("txt_Id") as ASPxTextBox;
string id = SafeValue.SafeString(txt_Id.Text, "");
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(RefLc), "Id='" + id + "'");
RefLc lc = C2.Manager.ORManager.GetObject(query) as RefLc;
ASPxDateEdit date_AppDate = grid.FindEditFormTemplateControl("date_AppDate") as ASPxDateEdit;
bool isNew = false;
//const string runType = "DOOUT";
string lcNo = "";
if (lc == null)
{
lc = new RefLc();
isNew = true;
lcNo = C2Setup.GetNextNo("", "LetterOfCredit", date_AppDate.Date);
lc.LcAppDate = date_AppDate.Date;
}
ASPxComboBox cmb_Type = grid.FindEditFormTemplateControl("cmb_Type") as ASPxComboBox;
lc.LcType = cmb_Type.Text;
ASPxDateEdit date_LcExpirtDate = grid.FindEditFormTemplateControl("date_LcExpirtDate") as ASPxDateEdit;
lc.LcExpirtDate = date_LcExpirtDate.Date;
//Main Info
ASPxTextBox txt_TempNo = grid.FindEditFormTemplateControl("txt_TempNo") as ASPxTextBox;
lc.LcTempNo = txt_TempNo.Text;
ASPxTextBox txt_LcRefNo = grid.FindEditFormTemplateControl("txt_LcRefNo") as ASPxTextBox;
lc.LcRef = txt_LcRefNo.Text;
ASPxTextBox txt_LcExpirtPlace = grid.FindEditFormTemplateControl("txt_LcExpirtPlace") as ASPxTextBox;
lc.LcExpirtPlace = txt_LcExpirtPlace.Text;
ASPxButtonEdit txt_EntityCode = grid.FindEditFormTemplateControl("txt_EntityCode") as ASPxButtonEdit;
lc.LcEntityCode = txt_EntityCode.Text;
ASPxTextBox txt_EntityName = grid.FindEditFormTemplateControl("txt_EntityName") as ASPxTextBox;
lc.LcEntityName = txt_EntityName.Text;
ASPxMemo memo_EntityAddress = grid.FindEditFormTemplateControl("memo_EntityAddress") as ASPxMemo;
lc.LcEntityAddress = memo_EntityAddress.Text;
ASPxButtonEdit btn_BeneCode = grid.FindEditFormTemplateControl("btn_BeneCode") as ASPxButtonEdit;
lc.LcBeneCode = btn_BeneCode.Text;
ASPxTextBox txt_LcBeneName = grid.FindEditFormTemplateControl("txt_LcBeneName") as ASPxTextBox;
lc.LcBeneName = txt_LcBeneName.Text;
ASPxMemo memo_BeneAddress = grid.FindEditFormTemplateControl("memo_BeneAddress") as ASPxMemo;
lc.LcBeneAddress = memo_BeneAddress.Text;
ASPxButtonEdit btn_BankCode = grid.FindEditFormTemplateControl("btn_BankCode") as ASPxButtonEdit;
lc.BankCode = btn_BankCode.Text;
ASPxTextBox txt_BankName = grid.FindEditFormTemplateControl("txt_BankName") as ASPxTextBox;
lc.BankName = txt_BankName.Text;
ASPxMemo memo_BankAddress = grid.FindEditFormTemplateControl("memo_BankAddress") as ASPxMemo;
lc.BankAddress = memo_BankAddress.Text;
ASPxTextBox txt_Branch = grid.FindEditFormTemplateControl("txt_Branch") as ASPxTextBox;
lc.BankBranch = txt_Branch.Text;
ASPxTextBox txt_BankRef = grid.FindEditFormTemplateControl("txt_BankRef") as ASPxTextBox;
lc.BankRef = txt_BankRef.Text;
ASPxSpinEdit spin_BankTenor = grid.FindEditFormTemplateControl("spin_BankTenor") as ASPxSpinEdit;
lc.BankTenor = SafeValue.SafeInt(spin_BankTenor.Text, 0);
ASPxSpinEdit spin_LcAmount = grid.FindEditFormTemplateControl("spin_LcAmount") as ASPxSpinEdit;
lc.LcAmount = SafeValue.SafeDecimal(spin_LcAmount.Value, 1);
ASPxButtonEdit currency = grid.FindEditFormTemplateControl("txt_Currency") as ASPxButtonEdit;
lc.LcCurrency = currency.Text;
ASPxSpinEdit exRate = grid.FindEditFormTemplateControl("spin_ExRate") as ASPxSpinEdit;
lc.LcExRate = SafeValue.SafeDecimal(exRate.Value, 1);
ASPxTextBox txt_LcMode = grid.FindEditFormTemplateControl("txt_LcMode") as ASPxTextBox;
lc.LcMode = txt_LcMode.Text;
ASPxComboBox cmb_Status = grid.FindEditFormTemplateControl("cmb_Status") as ASPxComboBox;
lc.StatusCode = cmb_Status.Text;
string userId = HttpContext.Current.User.Identity.Name;
if (isNew)
{
lc.CreateBy = userId;
lc.CreateDateTime = DateTime.Now;
lc.LcNo = lcNo;
Manager.ORManager.StartTracking(lc, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(lc);
C2Setup.SetNextNo("", "LetterOfCredit", lcNo, date_AppDate.Date);
}
else
{
lc.UpdateBy = userId;
lc.UpdateDateTime = DateTime.Now;
bool isAddLog = false;
if (lc.StatusCode == SafeValue.SafeString(ConnectSql.ExecuteScalar("Select StatusCode from Ref_LC where LcNo='" + lc.LcNo + "'")))
{
}
else
{
isAddLog = true;
}
Manager.ORManager.StartTracking(lc, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(lc);
if (isAddLog)
{
EzshipLog.Log(lc.LcNo, "", lc.LcType, lc.StatusCode);
}
}
Session["RefLC"] = "LcNo='" + lc.LcNo + "'";
this.dsLc.FilterExpression = Session["RefLC"].ToString();
if (this.grid.GetRow(0) != null)
{
this.grid.StartEdit(0);
}
return(lc.LcNo);
}
catch { }
return("");
}
protected bool VilaParty(string code, string uen, string ic, string email1, string email2, string tel1, string tel2, string mobile1, string mobile2, string address, string responsible)
{
bool action = false;
string sql = string.Format(@"select top 1 SequenceId from XXParty order by SequenceId desc");
int sequenceId = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (responsible == "个人")
{
sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and CrNo='{1}' and Tel1='{2}'", code, ic, tel1);
}
else
{
sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and CrNo='{1}' and Tel1='{2}'", code, uen, tel1);
}
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (n > 0)
{
action = true;
}
else
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
sequenceId = sequenceId + 1;
#region list add
if (code.Length > 4)
{
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code + "_" + sequenceId, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code.Substring(0, 6) + "_" + sequenceId, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code + DateTime.Today.Second, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Status", "USE", SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", mobile1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", mobile2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (ic.Length > 0)
{
cpar = new ConnectSql_mb.cmdParameters("@CrNo", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
if (uen.Length > 0)
{
cpar = new ConnectSql_mb.cmdParameters("@CrNo", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
sql = string.Format(@"insert into XXParty(PartyId,Code,Name,IsCustomer,Status,Address,Tel1,Tel2,Fax1,Fax2,Email1,Email2,CrNo) values(@PartyId,@Code,@Name,1,@Status,@Address,@Tel1,@Tel2,@Fax1,@Fax2,@Email1,@Email2,@CrNo)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
else
{
}
#endregion
}
return(action);
}
protected void grid_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string s = e.Parameters;
if (s == "Save")
{
e.Result = SaveLc();
ASPxComboBox cmb_Type = grid.FindEditFormTemplateControl("cmb_Type") as ASPxComboBox;
ASPxTextBox txt_LcNo = this.grid.FindEditFormTemplateControl("txt_LcNo") as ASPxTextBox;
string type = SafeValue.SafeString(cmb_Type.Text);
bool isAddLog = false;
if (type == "Confirmed")
{
ASPxButtonEdit btn_BankCode = grid.FindEditFormTemplateControl("btn_BankCode") as ASPxButtonEdit;
string bankCode = SafeValue.SafeString(btn_BankCode.Text);
string sql = string.Format(@"select LoanFund from Lc_BankFunds where BankCode='{0}'", bankCode);
decimal loanFund = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql));
ASPxSpinEdit spin_LcAmount = grid.FindEditFormTemplateControl("spin_LcAmount") as ASPxSpinEdit;
decimal amount = SafeValue.SafeDecimal(spin_LcAmount.Text);
sql = string.Format(@"select sum(LoanFund) from Ref_LC where BankCode='{0}'", bankCode);
decimal totalAmount = SafeValue.SafeDecimal(C2.Manager.ORManager.ExecuteScalar(sql));
decimal space = SafeValue.SafeDecimal((loanFund - (amount + totalAmount)));
if (space < 0)
{
e.Result = "No Enough Amount";
}
}
if (cmb_Type.Text == SafeValue.SafeString(ConnectSql.ExecuteScalar("Select RefType from LcActivity where RefNo='" + txt_LcNo.Text + "'")))
{
}
else
{
isAddLog = true;
}
if (isAddLog)
{
if (type == "IMPORT LC")
{
EzshipLog.Activity(txt_LcNo.Text, "", "IMPORT LC", "RECEIVING");
}
if (type == "EXPORT LC")
{
EzshipLog.Activity(txt_LcNo.Text, "", "EXPORT LC", "ISSUING");
}
if (type == "STANDBY LC")
{
EzshipLog.Activity(txt_LcNo.Text, "", "EXPORT LC", "COLLECTION");
}
if (type == "BANK GUARANTEE")
{
EzshipLog.Activity(txt_LcNo.Text, "", "BANK GUARANTEE", "REMARK");
}
if (type == "SHIPPING GUARANTEE")
{
EzshipLog.Activity(txt_LcNo.Text, "", "SHIPPING GUARANTEE", "REMARK");
}
if (type == "OTHERS")
{
EzshipLog.Activity(txt_LcNo.Text, "", "OTHERS", "OTHERS");
}
}
}
}
public static DataSet DsImpTs(string orderNo, string docType)
{
DataSet set = new DataSet();
if (docType == "IV" || docType == "IV1")
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAArInvoice), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAArInvoice obj = objSet[0] as C2.XAArInvoice;
#region Invoice
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
string mastRefNo = obj.MastRefNo;
string mastType = obj.MastType;
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + mastRefNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
if (ctmJob != null)
{
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd.MM.yy");
row["CustRefNo"] = ctmJob.ClientRefNo;
row["Pol"] = ctmJob.Pol;
row["Pod"] = ctmJob.Pod;
row["CrBkgNo"] = ctmJob.CarrierBkgNo;
row["HblNo"] = ctmJob.CarrierBlNo;
}
row["JobNo"] = mastRefNo;
row["BillType"] = "TAX INVOICE";
row["DocNo"] = obj.DocNo;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["DocDueDate"] = obj.DocDueDate.ToString("dd/MM/yyyy");
row["UserId"] = HttpContext.Current.User.Identity.Name;
row["PartyName"] = obj.PartyName;
row["PartyTo"] = obj.PartyTo;
string sql = string.Format(@"select Address,Contact1,Fax1,Tel1,CrNo from XXParty where PartyId='{0}'", obj.PartyTo);
DataTable tab = ConnectSql_mb.GetDataTable(sql);
if (tab.Rows.Count > 0)
{
row["PartyAdd"] = tab.Rows[0]["Address"].ToString();
row["Contact1"] = tab.Rows[0]["Contact1"].ToString();
row["Fax1"] = tab.Rows[0]["Fax1"].ToString();
row["Tel1"] = tab.Rows[0]["Tel1"].ToString();
row["CrNo"] = tab.Rows[0]["CrNo"].ToString();
}
row["Terms"] = obj.Term;
row["Terms"] = obj.Term;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["MoneyWords"] = "";
row["Remark"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format(@"select Description from XAArInvoice where MastRefNo='{0}'", mastRefNo)));
decimal subTotal = 0;
decimal gstTotal = obj.TaxAmt;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select ChgCode,Qty,MastType,DocNo,ChgDes1,Price,Gst,GstAmt,Unit,
LineLocAmt, Currency,ExRate,
LocAmt,JobRefNo,GstType from XAArInvoiceDet where DocId={0} order by Gst desc", obj.SequenceId);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string cntType = SafeValue.SafeString(det.Rows[i]["JobRefNo"]);
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["No"] = i + 1;
row1["DocNo"] = SafeValue.SafeString(det.Rows[i]["DocNo"]);
row1["ChgDes1"] = SafeValue.SafeString(det.Rows[i]["ChgDes1"]);
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["LineAmt"] = SafeValue.SafeString(det.Rows[i]["LineLocAmt"]);
row1["Unit"] = SafeValue.SafeString(det.Rows[i]["Unit"]);
row1["Currency"] = SafeValue.SafeString(det.Rows[i]["Currency"]);
row1["ExRate"] = SafeValue.SafeString(det.Rows[i]["ExRate"]);
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(SafeValue.SafeDecimal(det.Rows[i]["Gst"]) * 100, 0) + "% " + det.Rows[i]["GstType"] + "R";
decimal amt = SafeValue.ChinaRound(SafeValue.SafeInt(det.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(det.Rows[i]["Gst"], 0)), 2);
row1["Amt"] = amt;
row1["GstAmt"] = SafeValue.SafeString(det.Rows[i]["GstAmt"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
row1["Qty"] = SafeValue.SafeInt(det.Rows[i]["Qty"], 0);
details.Rows.Add(row1);
subTotal += amt;
}
sql = string.Format(@"select ContainerNo,SealNo,ContainerType from CTM_JobDet1 where JobNo='{0}' ", mastRefNo);
DataTable cnt = ConnectSql.GetTab(sql);
for (int i = 0; i < cnt.Rows.Count; i++)
{
if (cnt.Rows.Count - i > 1)
{
row["ContainerNo"] += SafeValue.SafeString(cnt.Rows[i]["ContainerNo"]) + " / " + SafeValue.SafeString(cnt.Rows[i]["SealNo"])
+ " / " + SafeValue.SafeString(cnt.Rows[i]["ContainerType"]) + "\n";
}
else
{
row["ContainerNo"] += SafeValue.SafeString(cnt.Rows[i]["ContainerNo"]) + " / " + SafeValue.SafeString(cnt.Rows[i]["SealNo"])
+ " / " + SafeValue.SafeString(cnt.Rows[i]["ContainerType"]);
}
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["TotalAmt"] = subTotal + gstTotal;
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(details);
}
if (docType == "PL" || docType == "PL1")
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAApPayable), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAApPayable obj = objSet[0] as C2.XAApPayable;
#region PL
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
string mastRefNo = obj.MastRefNo;
string mastType = obj.MastType;
if (mastType == "CTM")
{
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + mastRefNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd.MM.yy");
}
row["DocNo"] = obj.DocNo;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["SupplierBillNo"] = obj.SupplierBillNo;
row["SupplierBillDate"] = obj.SupplierBillDate.ToString("dd/MM/yyyy");
row["PartyName"] = obj.PartyName;
string sql = string.Format(@"select Address from XXParty where PartyId='{0}'", obj.PartyTo);
row["PartyAdd"] = C2.Manager.ORManager.ExecuteScalar(sql);
row["Terms"] = obj.Term;
decimal subTotal = 0;
decimal gstTotal = 0;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select ChgCode,sum(Qty) as Qty,MastType,max(DocNo) DocNo,max(ChgDes1) as ChgDes1,Price,max(Gst) Gst,max(GstAmt) GstAmt,
max(LocAmt) LocAmt,max(JobRefNo) JobRefNo,max(GstType) GstType from XAApPayableDet where DocId={0} group by ChgCode,MastType,Price order by Gst desc", obj.SequenceId);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string cntType = SafeValue.SafeString(det.Rows[i]["JobRefNo"]);
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["DocNo"] = SafeValue.SafeString(det.Rows[i]["DocNo"]);
row1["ChgDes1"] = SafeValue.SafeString(det.Rows[i]["ChgDes1"]);
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(SafeValue.SafeDecimal(det.Rows[i]["Gst"]) * 100, 0) + "% " + det.Rows[i]["GstType"] + "R";
decimal amt = SafeValue.ChinaRound(SafeValue.SafeInt(det.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(det.Rows[i]["Gst"], 0)), 2);
row1["Amt"] = amt;
row1["GstAmt"] = SafeValue.SafeString(det.Rows[i]["GstAmt"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
row1["Qty"] = SafeValue.SafeInt(det.Rows[i]["Qty"], 0);
details.Rows.Add(row1);
subTotal += amt;
gstTotal += gstAmt;
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["Total"] = subTotal + gstTotal;
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(details);
}
return(set);
}
protected void grid_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string s = e.Parameters;
if (s == "Add")
{
string date = txt_from.Date.ToString("yyyy-MM-dd");
string to = txt_to.Date.ToString("yyyy-MM-dd");
string sql = string.Format(@"select RefNo,JobDate,JobNo from JobSchedule where CONVERT(VARCHAR(10),JobDate,120) between '{0}' and '{1}'", date, to);
DataTable tab_job = ConnectSql.GetTab(sql);
int res = 0;
string payDate = txt_PayDate.Date.ToString("yyyy-MM-dd");
if (tab_job.Rows.Count > 0)
{
string refNoStr = "";
for (int m = 0; m < tab_job.Rows.Count; m++)
{
DateTime jobDate = SafeValue.SafeDate(tab_job.Rows[m]["JobDate"], DateTime.Today);
string jobNo = SafeValue.SafeString(tab_job.Rows[m]["JobNo"]);
string refNo = SafeValue.SafeString(tab_job.Rows[m]["RefNo"]);
sql = string.Format(@"select * from JobCrews where RefNo='{0}' and HrRole='Casual' and IsPay='N'", jobNo);
DataTable tab = ConnectSql.GetTab(sql);
for (int i = 0; i < tab.Rows.Count; i++)
{
int id = SafeValue.SafeInt(tab.Rows[i]["Id"], 0);
string code = SafeValue.SafeString(tab.Rows[i]["Code"]);
string name = SafeValue.SafeString(tab.Rows[i]["Name"]);
string tel = SafeValue.SafeString(tab.Rows[i]["Tel"]);
string hrRole = SafeValue.SafeString(tab.Rows[i]["HrRole"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
sql = string.Format(@"select count(*) from JobCrews where Remark='{0}' and HrRole='Casual' and Code='{2}' and CONVERT(varchar(100), JobTime, 23)='{1}' and Status='Pay'", remark, jobDate.ToString("yyyy-MM-dd"), code);
int cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
sql = string.Format(@"select HrGroup from Hr_Person where Name='{0}'", name);
string type = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
if (type == "DAY")
{
if (cnt == 0)
{
refNoStr = GetJobNo(name, jobDate.ToString("yyyy-MM-dd"));
sql = string.Format(@"select (case when isnull(Amount1,0)=0 then 40 else Amount1 end) as Amount1,
(case when isnull(Amount2,0)=0 then 20 else Amount2 end) as Amount2,
(case when isnull(Amount3,0)=0 then 5 else Amount3 end) as Amount3 from Hr_Person where Name='{0}' and IcNo='{1}'", name, code);
DataTable tab_amt = ConnectSql.GetTab(sql);
decimal amt1 = SafeValue.SafeDecimal(tab_amt.Rows[0]["Amount1"]);
decimal amt2 = SafeValue.SafeDecimal(tab_amt.Rows[0]["Amount2"]);
decimal amt3 = SafeValue.SafeDecimal(tab_amt.Rows[0]["Amount3"]);
sql = string.Format(@"insert into JobCrews(RefNo,Code,Name,Tel,Status,JobTime,PayDate,IsPay,Amount1,Amount2,Amount3,Amount4,Amount5,Amount6,Amount7,Remark,HrRole) values('{0}','{1}','{2}','{3}','Pay','{4}','{5}','Y',{7},{8},0,0,0,{6},{9},'{10}','{11}')", refNoStr, code, name, tel, jobDate, payDate, amt3, amt1, amt2, (amt1 + amt2), remark, hrRole);
res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
sql = string.Format(@"update JobCrews set IsPay='Y' where Id={0}", id);
Manager.ORManager.ExecuteCommand(sql);
e.Result = "Success";
}
}
else
{
sql = string.Format(@"update JobCrews set IsPay='Y' where Id={0}", id);
Manager.ORManager.ExecuteCommand(sql);
}
}
if (type == "JOB")
{
sql = string.Format(@"select RefNo from JobSchedule where JobNo='{0}'", jobNo);
refNoStr = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql));
sql = string.Format(@"select (case when isnull(Amount1,0)=0 then 40 else Amount1 end) as Amount1,
(case when isnull(Amount2,0)=0 then 20 else Amount2 end) as Amount2,
(case when isnull(Amount3,0)=0 then 5 else Amount3 end) as Amount3 from Hr_Person where Name='{0}' and IcNo='{1}'", name, code);
DataTable tab_amt = ConnectSql.GetTab(sql);
decimal amt1 = SafeValue.SafeDecimal(tab_amt.Rows[0]["Amount1"]);
decimal amt2 = SafeValue.SafeDecimal(tab_amt.Rows[0]["Amount2"]);
decimal amt3 = SafeValue.SafeDecimal(tab_amt.Rows[0]["Amount3"]);
sql = string.Format(@"insert into JobCrews(RefNo,Code,Name,Tel,Status,JobTime,PayDate,IsPay,Amount1,Amount2,Amount3,Amount4,Amount5,Amount6,Amount7,Remark,HrRole) values('{0}','{1}','{2}','{3}','Pay','{4}','{5}','Y',{7},{8},0,0,0,{6},{9},'{10}','{11}')", refNoStr, code, name, tel, jobDate, payDate, amt3, amt1, amt2, (amt1 + amt2), remark, hrRole);
res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
sql = string.Format(@"update JobCrews set IsPay='Y' where Id={0}", id);
Manager.ORManager.ExecuteCommand(sql);
e.Result = "Success";
}
}
}
}
}
if (tab_job.Rows.Count == 0)
{
e.Result = "Fail";
}
}
}
public void ImportJob(string batch, string file, out string error_text)
{
//throw new Exception("importjob");
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
//Workbook wb = new Workbook();
byte[] byData = new byte[100];
char[] charData = new char[1000];
List <string> list = new List <string>();
if (file.ToLower().EndsWith(".txt"))
{
using (System.IO.StreamReader sr = new System.IO.StreamReader(file, true))
{
string str;
while ((str = sr.ReadLine()) != null)
{
list.Add(str);
}
}
}
int empty_i = 0;
string re_text = "";
//Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
string notInside = "";
int insideDo = 0;
string sql = "";
//throw new Exception("here");
//=================================== version 1
bool beginImport = false;
int _row = 0;
DateTime payDate = DateTime.Today;
string value = "";
string cur = "SGD";
int docId = 0;
int index = 0;
for (int i = 0; i < list.Count; i++)
{
_row++;
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string line = list[i];
string[] ar = line.Split('~');
if (i == 0)
{
ar = line.Split(' ');
payDate = SafeValue.SafeDate(ar[7], date);
docId = InsertPay(payDate, cur);
}
if (i == list.Count - 1)
{
ar = line.Split('~');
decimal locAmt = SafeValue.SafeDecimal(ar[4]);
sql = string.Format(@"update XAApPayment set DocAmt={1}, LocAmt={1} where SequenceId={0}", docId, locAmt);
ConnectSql_mb.ExecuteNonQuery(sql);
continue;
}
if (i == 4)
{
ar = line.Split(':');
cur = ar[1];
}
if (ar[0] == "Invoice No.")
{
beginImport = true;
empty_i++;
continue;
}
//throw new Exception(ws.Cells["A" + i].Type.ToString());
try
{
value = list[i];
}
catch
{
empty_i++;
continue;
}
if (line.Length <= 0)
{
empty_i++;
continue;
}
empty_i = 0;
if (beginImport)
{
index++;
string invNo = SafeValue.SafeString(ar[0]);
DateTime invDate = SafeValue.SafeDate(ar[1], date);
//string docType = SafeValue.SafeString(ar[2]);
decimal amt = SafeValue.SafeDecimal(ar[4]);
//=====================检测是否存在Bill Number
string billDate = invDate.ToString("yyyy-MM-dd");
string sql_check = string.Format(@"select count(*) from XAApPaymentDet where Remark1='{0}'", invNo);
int check_n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
if (check_n > 0)
{
existDo++;
continue;
}
else
{
try
{
sql_check = string.Format(@"select SequenceId,DocNo,DocType,CurrencyId,DocDate from XAApPayable where SupplierBillNo='{0}' and SupplierBillDate='{1}'", invNo, billDate);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
string docNo = SafeValue.SafeString(dt_check.Rows[0]["DocNo"]);
string docType = SafeValue.SafeString(dt_check.Rows[0]["DocType"]);
DateTime docDate = SafeValue.SafeDate(dt_check.Rows[0]["DocDate"], DateTime.Today);
InsertPay_Det(docId, docNo, docType, index, invNo, "", "", 1, amt, cur, 1, 0, "", "", "", "", invDate);
successJob++;
}
else
{
insideDo++;
if (insideDo == 1)
{
notInside += invNo;
}
else
{
notInside += invNo + ",";
}
}
}
catch (Exception ex)
{
//errorDo++;
throw new Exception(_row.ToString() + "##" + ex.Message);
}
}
}
else
{
}
}
sql = string.Format(@"select DocNo,DocDate,LocAmt from XAApPayment where SequenceId={0}", docId);
DataTable dt_pay = ConnectSql_mb.GetDataTable(sql);
string billNo = "";
decimal totalAmt = 0;
DateTime docDate1 = DateTime.Today;
if (dt_pay.Rows.Count > 0)
{
billNo = SafeValue_mb.SafeString(dt_pay.Rows[0]["DocNo"]);
totalAmt = SafeValue.SafeDecimal(dt_pay.Rows[0]["LocAmt"]);
docDate1 = SafeValue.SafeDate(dt_pay.Rows[0]["DocDate"], DateTime.Today);
}
re_text = string.Format(@"uploaded {0} lines", successJob);
re_text += successItem > 0 ? string.Format(@" {0} items.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} existed", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} error", errorDo) : "";
re_text += insideDo > 0 ? string.Format(@" ,{0} not inside the payable", "Invoice:【" + notInside + "】") : "";
error_text = re_text;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark,Note1,Note2,Value1) values (getdate(),@Controller,@JobType,@Remark,@Note1,@Note2,@Value1)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "GIRO", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", re_text, SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Note1", billNo, SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Note2", docDate1.ToString("yyyy-MM-dd"), SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Value1", totalAmt, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
private void InsertInv_Det(int docId, string docNo, int index, string sku, string lotNo, string des, int qty, decimal price, string cur, decimal exRate, decimal gst, string soNo, string doNo, string mastType, string docType)
{
try
{
C2.XAArInvoiceDet det = new C2.XAArInvoiceDet();
det.DocId = docId;
det.DocLineNo = index;
det.DocNo = docNo;
det.DocType = docType;
det.AcCode = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("select ArCode from ref_product where Code='{0}'", sku)), System.Configuration.ConfigurationManager.AppSettings["LocalArCode"]);
det.AcSource = "CR";
det.MastRefNo = soNo;
det.JobRefNo = lotNo;
if (doNo.Length > 0)
{
det.MastRefNo = doNo;
det.JobRefNo = "";
}
det.MastType = mastType;
det.SplitType = "";
det.ChgCode = sku + "(Product)";
det.ChgDes1 = des;
det.ChgDes2 = "";
det.ChgDes3 = "";
det.Price = price;
det.Qty = qty;
det.Unit = "";
det.Currency = cur;
det.ExRate = 1;
det.Gst = gst;
if (det.ExRate == 0)
{
det.ExRate = 1;
}
if (det.Gst > 0)
{
det.GstType = "S";
}
else if (det.Currency == System.Configuration.ConfigurationManager.AppSettings["Currency"])
{
det.GstType = "E";
}
else
{
det.GstType = "Z";
}
decimal amt = SafeValue.ChinaRound(det.Qty * det.Price, 2);
decimal gstAmt = SafeValue.ChinaRound((amt * det.Gst), 2);
decimal docAmt = amt + gstAmt;
decimal locAmt = SafeValue.ChinaRound(docAmt * det.ExRate, 2);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
det.OtherAmt = 0;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
catch
{
}
}
protected void btn_CreateInv_Click(object sender, EventArgs e)
{
string invNo = "";
string mastType = "WH";
DateTime dt = DateTime.Today;
bool isNew = false;
int docId = 0;
if (list.Count > 0)
{
XAArInvoice iv = null;
for (int i = 0; i < list.Count; i++)
{
string counterType = "AR-IV";
string partyId = list[i].partyId;
string product = list[i].doNo;
string doNo = list[i].doNo;
int qty = list[i].qty;
decimal surcharge = list[i].surcharge;
string sql_cost = string.Format(@"select * from(select mast.DoNo,CONVERT(VARCHAR(10),mast.DoDate,110) as DoDate,cost.ChgCode as ChargeCode,
ChgCodeDes as Description,CONVERT(int,CostQty) as Qty,CostPrice as Price,StatusCode,
isnull(((CostQty)*(isnull((CostPrice),0) )),0) as TotalAmt,dbo.fun_GetPartyName(mast.PartyId) AS PartyName,mast.PartyId,
0 as Surcharge,0 as SurchageAmt,JobType as DoType,CONVERT(decimal(10,6),(isnull((CostQty*CostPrice),0)*CostGst)) as GstAmt,
ISNULL((select count(SequenceId) from XAArInvoiceDet det where det.ChgCode=cost.ChgCode and det.MastRefNo=RefNo),0) as CostCnt,
isnull((select count(Id) from Wh_Costing where RefNo=mast.DoNo and JobType=mast.DoType),0) as CostDoCnt
from Wh_Costing cost left join Wh_DO mast on RefNo=mast.DoNo and JobType=mast.DoType and StatusCode='CLS'
) as tab where CostCnt=0 and CostDoCnt>0 and TotalAmt>0 and DoNo='{0}'", doNo);
string sql = string.Format(@"select top 1 det.DocNo from XAArInvoiceDet det inner join XAArInvoice mast on det.DocNo=mast.DocNo where det.MastRefNo='{0}' and InvType='HANDLING' and CancelInd='N' order by det.DocNo desc", doNo);
invNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql));
if (invNo.Length == 0)
{
iv = new XAArInvoice();
invNo = C2Setup.GetNextNo("", counterType, dt);
isNew = true;
}
else
{
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(XAArInvoice), "DocNo='" + invNo + "'");
iv = C2.Manager.ORManager.GetObject(query) as XAArInvoice;
isNew = false;
}
iv.DocType = "IV";
iv.DocDate = dt;
iv.DocNo = invNo;
iv.PartyTo = partyId;
iv.CurrencyId = System.Configuration.ConfigurationManager.AppSettings["Currency"];
iv.ExRate = 1;
iv.AcCode = EzshipHelper.GetAccApCode(iv.PartyTo, iv.CurrencyId);
iv.AcSource = "DB";
iv.Description = "";
iv.Term = "CASH";
string[] currentPeriod = EzshipHelper.GetAccPeriod(iv.DocDate);
iv.AcYear = SafeValue.SafeInt(currentPeriod[1], iv.DocDate.Year);
iv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], iv.DocDate.Month);
iv.MastRefNo = "";
iv.JobRefNo = "";
iv.MastType = mastType;
iv.DocAmt = 0;
iv.LocAmt = 0;
iv.BalanceAmt = 0;
iv.CancelDate = new DateTime(1900, 1, 1);
iv.CancelInd = "N";
iv.DocDueDate = dt;
iv.ExportInd = "N";
iv.SpecialNote = "";
iv.UserId = EzshipHelper.GetUserName();
iv.EntryDate = DateTime.Now;
iv.InvType = "HANDLING";
if (isNew)
{
C2.Manager.ORManager.StartTracking(iv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(iv);
C2Setup.SetNextNo(iv.DocType, counterType, invNo, iv.DocDate);
}
else
{
Manager.ORManager.StartTracking(iv, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(iv);
}
try
{
DataTable tab_Cost = ConnectSql.GetTab(sql_cost);
for (int a = 0; a < tab_Cost.Rows.Count; a++)
{
C2.XAArInvoiceDet det = new C2.XAArInvoiceDet();
det.DocId = iv.SequenceId;
det.DocLineNo = i + 1;
det.DocNo = invNo;
det.DocType = "IV";
det.AcCode = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format("select ArCode from ref_product where Code='{0}'", product)), System.Configuration.ConfigurationManager.AppSettings["ItemArCode"]);
det.AcSource = "CR";
det.MastRefNo = doNo;
det.JobRefNo = "";
det.MastType = mastType;
det.SplitType = "";
det.ChgCode = SafeValue.SafeString(tab_Cost.Rows[a]["ChargeCode"]);;
det.ChgDes1 = SafeValue.SafeString(tab_Cost.Rows[a]["Description"]);
det.ChgDes2 = "";
det.ChgDes3 = "";
det.Price = SafeValue.SafeDecimal(tab_Cost.Rows[a]["Price"]);
det.Qty = SafeValue.SafeInt(tab_Cost.Rows[a]["Qty"], 0);
det.Unit = "";
det.Currency = iv.CurrencyId;
det.ExRate = 1;
det.Gst = 0;
if (det.ExRate == 0)
{
det.ExRate = 1;
}
if (det.Gst > 0)
{
det.GstType = "S";
}
else if (det.Currency == System.Configuration.ConfigurationManager.AppSettings["Currency"])
{
det.GstType = "E";
}
else
{
det.GstType = "Z";
}
decimal amt = SafeValue.ChinaRound(det.Qty * det.Price, 2);
decimal gstAmt = SafeValue.ChinaRound((amt * det.Gst), 2);
decimal docAmt = amt + gstAmt;
decimal locAmt = SafeValue.ChinaRound(docAmt * det.ExRate, 2);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
det.OtherAmt = 0;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
if (surcharge != 0)
{
det.ChgCode = "HANDLE";
det.Qty = 1;
det.Price = surcharge;
sql = string.Format(@"select * from XXChgCode where ChgcodeId='{0}'", det.ChgCode);
DataTable tab = ConnectSql.GetTab(sql);
for (int j = 0; j < tab.Rows.Count; j++)
{
det.AcCode = SafeValue.SafeString(tab.Rows[j]["ArCode"]);
det.ChgDes1 = SafeValue.SafeString(tab.Rows[j]["ChgcodeDes"]);
det.GstType = SafeValue.SafeString(tab.Rows[j]["GstTypeId"]);
det.Gst = SafeValue.SafeDecimal(tab.Rows[j]["GstP"]);
}
amt = SafeValue.ChinaRound(det.Qty * det.Price, 2);
gstAmt = SafeValue.ChinaRound((amt * det.Gst), 2);
docAmt = amt + gstAmt;
locAmt = SafeValue.ChinaRound(docAmt * det.ExRate, 2);
det.GstAmt = gstAmt;
det.DocAmt = docAmt;
det.LocAmt = locAmt;
C2.Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(det);
}
docId = iv.SequenceId;
}
}
catch
{
}
UpdateMaster(docId);
}
// string script = string.Format('<script type="text/javascript" > parent.navTab.openTab("{0}","/opsAccount/ArInvoiceEdit.aspx?no="{0}"",{title:'', fresh:false, external:true});</script>", invNo);
//string script = string.Format("<script type='text/javascript' >alert('{0}');</script>", refNo);
//Response.Clear();
//Response.Write(script);
//<a href='javascript: parent.navTab.openTab("<%# Eval("DoNo") %>","/WareHouse/Job/DoOutEdit.aspx?no=<%# Eval("DoNo") %>",{title:"<%# Eval("DoNo") %>", fresh:false, external:true});'><%# Eval("DoNo") %></a>
Response.Redirect("/opsAccount/ArInvoiceEdit.aspx?no=" + invNo);
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
ASPxTextBox Id = ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
string s = e.Parameters;
string[] ar = s.Split('_');
if (ar.Length >= 2)
{
if (ar[0].Equals("Printline"))
{
#region
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.ASPxGridView1.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
string sql = string.Format(@"select FromDate,ToDate,Person from Hr_Payroll where Id={0}", SafeValue.SafeInt(lbl_Id.Text, 0));
DataTable dt = ConnectSql.GetTab(sql);
string Person = "";
DateTime FromDate = DateTime.Now;
DateTime ToDate = DateTime.Now;
if (dt.Rows.Count > 0)
{
Person = SafeValue.SafeString(dt.Rows[0]["Person"]);
FromDate = SafeValue.SafeDate(dt.Rows[0]["FromDate"], DateTime.Now);
ToDate = SafeValue.SafeDate(dt.Rows[0]["ToDate"], DateTime.Now);
}
e.Result = FromDate + "," + ToDate + "," + lbl_Id.Text + "," + Person;
#endregion
}
if (ar[0].Equals("Editline"))
{
#region
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.ASPxGridView1.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
e.Result = lbl_Id.Text;
#endregion
}
if (ar[0].Equals("Deleteline"))
{
#region
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.ASPxGridView1.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
string sql = string.Format(@"delete from Hr_Payroll where Id='{0}'", SafeValue.SafeInt(lbl_Id.Text, 0));
int res = ConnectSql_mb.ExecuteNonQuery(sql);
if (res > 0)
{
e.Result = "Success";
}
#endregion
}
}
else
{
#region
if (s == "Confirm")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Confirm' where StatusCode='Draft'");
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "UnConfirm")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Draft' where Id={0}", SafeValue.SafeInt(Id.Text, 0));
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "UnCancel")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Draft' where Id={0}", SafeValue.SafeInt(Id.Text, 0));
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "Payroll")
{
string value = "Action Error! Pls check again";
string sql = string.Format(@"select Person,sum(Amt) as TotalAmt,max(Remark) as Remark from Hr_Quote where (person in (select Id from hr_person where Status='EMPLOYEE')) group by Person");
DataTable tab = ConnectSql.GetTab(sql);
int fromYear = txt_from.Date.Year;
int fromMonth = txt_from.Date.Month;
string name = HttpContext.Current.User.Identity.Name;
int toYear = txt_end.Date.Year;
int toMonth = txt_end.Date.Month;
bool result = false;
string from = txt_from.Date.ToString("yyyy-MM-dd");
string to = txt_end.Date.ToString("yyyy-MM-dd");
int month = toMonth - fromMonth;
DateTime firstDayOfFromMonth = new DateTime(fromYear, fromMonth, 1);
DateTime lastDayOfFromMonth = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
DateTime firstDayOfToMonth = new DateTime(toYear, toMonth, 1);
DateTime lastDayOfToMonth = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));
if (month < 2)
{
for (int a = 0; a <= month; a++)
{
for (int i = 0; i < tab.Rows.Count; i++)
{
int person = SafeValue.SafeInt(tab.Rows[i]["Person"], 0);
string sql_pay = string.Format(@"select count(*) from Hr_Payroll where Person={0} and (FromDate>='{1}' and ToDate<='{2}')", person, firstDayOfFromMonth.ToString("yyyy-MM-dd"), lastDayOfFromMonth.ToString("yyyy-MM-dd"));
int cnt = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_pay), 0);
if (cnt == 0)
{
#region From Date
decimal amt = SafeValue.SafeDecimal(tab.Rows[i]["TotalAmt"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
HrPayroll payroll = new HrPayroll();
payroll.Person = person;
payroll.FromDate = firstDayOfFromMonth;
payroll.ToDate = lastDayOfFromMonth;
payroll.StatusCode = "Draft";
payroll.Term = "";
payroll.Remark = "";
payroll.Pic = "";
payroll.CreateBy = name;
payroll.CreateDateTime = DateTime.Now;
payroll.Amt = amt;
payroll.AutoInd = "Yes";
Manager.ORManager.StartTracking(payroll, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(payroll);
sql = string.Format(@"select * from Hr_Quote where Person={0} ", person);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = SafeValue.SafeString(tabDet.Rows[j]["PayItem"]);
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})", payroll.Id, code, des.Replace("'", ""), payamt, name);
ConnectSql.ExecuteSql(sql);
}
auto_calculate(person, payroll.Id);
//InsertLeave(payroll.Id, person, from, to, name);
C2.HrPayroll.UpdateMaster(payroll.Id);
#endregion
result = true;
}
string sql_pay_to = string.Format(@"select count(*) from Hr_Payroll where Person={0} and (FromDate>='{1}' and ToDate<='{2}')", person, firstDayOfToMonth.ToString("yyyy-MM-dd"), lastDayOfToMonth.ToString("yyyy-MM-dd"));
int cnt_to = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_pay_to), 0);
if (cnt_to == 0)
{
#region To Date
decimal amt = SafeValue.SafeDecimal(tab.Rows[i]["TotalAmt"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
HrPayroll payroll = new HrPayroll();
payroll.Person = person;
payroll.FromDate = firstDayOfToMonth;
payroll.ToDate = lastDayOfToMonth;
payroll.StatusCode = "Draft";
payroll.Term = "";
payroll.Remark = "";
payroll.Pic = "";
payroll.CreateBy = name;
payroll.CreateDateTime = DateTime.Now;
payroll.Amt = amt;
payroll.AutoInd = "Yes";
Manager.ORManager.StartTracking(payroll, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(payroll);
sql = string.Format(@"select * from Hr_Quote where Person={0} ", person);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = SafeValue.SafeString(tabDet.Rows[j]["PayItem"]);
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})", payroll.Id, code, des, payamt, name);
ConnectSql.ExecuteSql(sql);
}
auto_calculate(person, payroll.Id);
InsertLeave(payroll.Id, person, from, to, name);
C2.HrPayroll.UpdateMaster(payroll.Id);
CalcCpf(payroll.Id);
#endregion
result = true;
}
}
}
if (result)
{
value = "Success!";
btn_Sch_Click(null, null);
}
}
e.Result = value;
}
#endregion
}
}