private static DataTable Pl_Mast(string refN, string userId)
{
string sql = string.Format(@"SELECT job.JobNo as RefN,convert(nvarchar(10),GetDate(),103) as NowD,job.CreateBy as UserId,'SGD' as Currency,1 as ExRate,JobType as JobType
,dbo.fun_GetPartyName(job.ClientId) as LocalCust,dbo.fun_GetPartyName(job.ClientId) as Agent,'{1}' as Company,JOB.CarrierBkgNo as Obl
,job.Vessel as Ves,convert(nvarchar(10),job.EtaDate,103) as Eta,dbo.fun_GetPortName(job.Pol) as Pol, dbo.fun_GetPortName(job.Pod) as Pod,'' ContN
,det1.Qty,det1.PackageType as Pack,det1.wT as Wt,det1.M3 as M3,0 as TsM3,0 as LocalM3
,job.Remark as Rmk
,isnull(inv.IV,0) Rev1
,0 as Rev2
,isnull(inv.DN,0) Rev3
,isnull(inv.CN,0) Rev4
,isnull(inv.IV+inv.DN-inv.CN,0) as Rev
,ISNULL(pay.Cost1,0) as Cost1
,0 as Cost2
,0 Cost3
,ISNULL(pay.Cost1,0) as Cost
,isnull(inv.IV+inv.DN-inv.CN,0)-ISNULL(pay.Cost1,0) as Profit
FROM CTM_Job as job
left outer join (select jobNo,sum(Qty) as Qty,sum(Weight) as Wt,sum(volume) as M3,MAX(PackageType) as PackageType from CTM_JobDet1 group by JobNo) as det1 on job.JobNo=det1.JobNo
left outer join (SELECT MastRefNo as RefNo,sum(case when DocType='IV' then LineLocAmt else 0 end) as IV,SUM(case when DocType='DN' then lineLocAmt else 0 end) as DN,SUM(case when DocType='CN' then lineLocAmt else 0 end) as CN
FROM XaArInvoiceDet WHERE MastRefNo = '{0}' and MastType = 'CTM' group by MastRefNo) as inv on inv.RefNo=job.JobNo
left outer join (select MastRefNo as RefNo,SUM(case DocType when 'PL' then LineLocAmt when 'SD' then LineLocAmt when 'VO' then LineLocAmt when 'SC' then -LineLocAmt else 0 end ) as Cost1
from XAApPayableDet where MastRefNo='{0}' and MastType = 'CTM' group by MastRefNo) as pay on pay.RefNo=job.JobNo
--left outer join (SELECT refNo as refNo,sum(CostLocAmt) as Cost3 FROM CTM_Costing WHERE RefNo='{0}' and JobType ='CTM' group by RefNo) as costing on costing.refNo=job.JobNo
where job.JobNo='{0}'", refN, System.Configuration.ConfigurationManager.AppSettings["CompanyName"]);
DataSet ds = ConnectSql.GetDataSet(sql);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0].Copy());
}
return(new DataTable());
}
private MemoryStream PrintWh_SQ(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";
DataSet set = new DataSet();
set.Tables.Add(Mast);
set.Tables.Add(Detail);
set.Relations.Add("Rela", Mast.Columns["Relation"], Detail.Columns["Relation"]);
rpt.DataSource = set;
rpt.CreateDocument();
rpt.ExportToPdf(str);
return(str);
}
protected void btn_EDI_Click(object sender, EventArgs e)
{
ASPxTextBox txtHouseNo = this.grid_Export.FindEditFormTemplateControl("txtHouseNo") as ASPxTextBox;
string jobNo = SafeValue.SafeString(txtHouseNo.Text);
string sql_mast = string.Format(@"select * from [dbo].[air_job] WHERE (JobNo = '{0}' )", jobNo);
DataTable tab_mast = ConnectSql.GetDataSet(sql_mast).Tables[0];
if (tab_mast.Rows.Count > 0 && jobNo.Length > 0)
{
ExportArToXml(jobNo, tab_mast);
}
}
private MemoryStream PrintWh_invoice(string refN, string refType, string userId)
{
string user = HttpContext.Current.User.Identity.Name;
XtraReport rpt = new XtraReport();
rpt.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\invoice.repx"));
string strsql = string.Format(@"exec proc_PrintInvoice_wh '{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();
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();
rpt_Inv.LoadLayout(Server.MapPath(@"~\ReportWarehouse\repx\invoice_2.repx"));
subReport_Inv.ReportSource = rpt_Inv;
rpt_Inv.DataSource = Detail2;
}
rpt.DataSource = Mast;
System.IO.MemoryStream str = new MemoryStream();
rpt.ExportToPdf(str);
return(str);
}
public static DataTable PrintBookingConfirm(string jobN)
{
DataTable dt = new DataTable();
try
{
string strsql = string.Format(@"exec proc_PrintAir_export_BookingConfirm '{0}','{1}','{2}','{3}','{4}'", "", jobN, "", "", System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
DataSet ds_temp = ConnectSql.GetDataSet(strsql);
dt = ds_temp.Tables[0].Copy();
}
catch (Exception ex) { }
return(dt);
}
public static DataTable dsSo(string jobN)
{
try
{
string strsql = string.Format(@"exec proc_PrintExpShippingOrder '{0}','{1}','{2}','{3}','{4}'", "", jobN, "AE", "", System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
DataSet ds_temp = ConnectSql.GetDataSet(strsql);
return(ds_temp.Tables[0]);
//DataTable Detail = ds_temp.Tables[0].Copy();
//Detail.TableName = "Detail";
//ds.Tables.Add(Detail);
}
catch (Exception ex) { }
return(null);
}
protected void cmb_loc_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
string s = cmb_WareHouse.SelectedItem.Value.ToString();
string Sql = "SELECT Id,Code,Name from ref_location where Loclevel='Unit' and WarehouseCode='" + s + "'";
DataSet ds = ConnectSql.GetDataSet(Sql);
cmb_loc.DataSource = ds.Tables[0];
cmb_loc.TextField = "Code";
cmb_loc.ValueField = "Code";
cmb_loc.DataBind();
}
protected void txt_ToLoc_Callback(object sender, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
{
ASPxComboBox txt_ToWh = this.ASPxGridView1.FindRowCellTemplateControl(0, (GridViewDataTextColumn)ASPxGridView1.Columns["ToWh"], "txt_ToWh") as ASPxComboBox;
ASPxComboBox txt_ToLoc = this.ASPxGridView1.FindRowCellTemplateControl(0, (GridViewDataTextColumn)ASPxGridView1.Columns["ToLoc"], "txt_ToLoc") as ASPxComboBox;
string s = txt_ToWh.SelectedItem.Value.ToString();
string Sql = "SELECT Id,Code,Name from ref_location where Loclevel='Unit' and WarehouseCode='" + s + "'";
DataSet ds = ConnectSql.GetDataSet(Sql);
txt_ToLoc.DataSource = ds.Tables[0];
txt_ToLoc.TextField = "Code";
txt_ToLoc.ValueField = "Code";
txt_ToLoc.DataBind();
}
public static DataSet PrintJobOrder(string exportN, string jobType)
{
DataSet set1 = new DataSet();
try
{
string strsql = string.Format(@"exec proc_PrintJobOrder1 '{0}','{1}','{2}','{3}','{4}'", exportN, "", jobType, "", System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
DataSet ds_temp = ConnectSql.GetDataSet(strsql);
DataTable mast = ds_temp.Tables[0].Copy();
mast.TableName = "Mast";
DataTable det = ds_temp.Tables[1].Copy();
det.TableName = "Detail";
set1.Tables.Add(mast);
set1.Tables.Add(det);
set1.Relations.Add("Rela", mast.Columns["JobNo"], det.Columns["JobNo"]);
}
catch (Exception ex) { }
return(set1);
}
public static DataSet dsDo(string jobN)
{
DataSet ds = new DataSet();
try
{
string strsql = string.Format(@"exec proc_PrintAir_import_DO '{0}','{1}','{2}','{3}','{4}'", "", jobN, "", "", System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
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";
ds.Tables.Add(Mast);
ds.Tables.Add(Detail);
DataRelation r = new DataRelation("", Mast.Columns["JobNo"], Detail.Columns["JobNo"]);
ds.Relations.Add(r);
}
catch (Exception ex) { }
return(ds);
}
public static DataSet PrintManifest(string refN)
{
DataSet ds = new DataSet();
try
{
string strsql = string.Format(@"exec proc_PrintAir_export_Manifest '{0}','{1}','{2}','{3}','{4}'", refN, "", "", "", "");
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";
ds.Tables.Add(Mast);
ds.Tables.Add(Detail);
DataRelation r = new DataRelation("", Mast.Columns["RefNo"], Detail.Columns["RefNo"]);
ds.Relations.Add(r);
}
catch (Exception ex) { }
return(ds);
}
public static XmlDocument Export_order(string orderNo, string orderType)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml("<?xml version=\"1.0\" encoding=\"GB2312\"?><Order></Order>");
XmlNode xmlNode = null;
string element = "Order";
string sql_mast = string.Format(@"Select Id,RelaId,DoNo,DoType,DoDate,PoNo,PoDate,PartyName as PartyName,PartyPostalCode as PartyPostal,PartyCity,PartyCountry,PartyAdd as PartyAddress,
CustomerReference as CustomsRef,CustomerDate as RefDate,PoNo,PoDate,PartyInvNo,PartyInvDate,PermitNo,PermitApprovalDate,TptMode,PalletizedInd,EquipNo,Personnel,
CollectFrom as PickFrom,DeliveryTo as DeliveryTo,Remark1,Remark2,Priority,ExpectedDate,DoStatus,
Vessel,Voyage,Obl as OceanBl,Hbl as HouseBl,Pol,Pod,Eta,Etd,EtaDest,Carrier,Vehicle,Coo,
AgentName as Agent,AgentTel,AgentContact,AgentAdd as AgentAddress,AgentZip,AgentCity,AgentCountry,
NotifyName as Notify,NotifyTel,NotifyContact,NotifyAdd as NotifyAddress,NotifyZip,NotifyCity,NotifyCountry,
ConsigneeName Cnee,ConsigneeTel CneeTel,ConsigneeContact CneeContact,ConsigneeAdd CneeAddress,ConsigneeZip as CneeZip,ConsigneeCity as CneeCity,ConsigneeCountry as CneeCountry,PermitBy,OtherPermit,ModelType from Wh_Do WHERE (DoNo = '{0}' ) and DoType='{1}'", orderNo, orderType);
string sql_job = string.Format(@"select Id,RelaId,BatchNo,CustomsLot,LotNo,ProductCode Sku,Des1 Des,Qty1 PackQty,Qty2 WholeQty,Qty3 LooseQty,Qty4 Expected,Qty5 Transit,QtyPackWhole,QtyWholeLoose,QtyLooseBase,Uom1 PackUom,Uom2 WholeUom,Uom3 LooseUom,Uom4 BaseUom,Price,Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10 from wh_DoDet WHERE (DoNo = '{0}' ) and DoType='{1}'", orderNo, orderType);
string sql_job1 = string.Format(@"select Id,RelaId,Product,Location,ProcessStatus,LotNo,BatchNo,CustomsLot,Des1 Des,Qty1 PackQty,Qty2 WholeQty,Qty3 LooseQty,QtyPackWhole,QtyWholeLoose,QtyLooseBase,Uom1 PackUom,Uom2 WholeUom,Uom3 LooseUom,Uom4 BaseUom,Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10 from wh_DoDet2 WHERE (DoNo = '{0}' ) and DoType='{1}'", orderNo, orderType);
string sql_job2 = string.Format(@"select Id,RelaId,ContainerNo,ContainerType,SealNo,Weight,M3,Qty,PkgType from Wh_DoDet3 WHERE (DoNo = '{0}' ) and DoType='{1}'", orderNo, orderType);
xmlDoc.LoadXml("<?xml version=\"1.0\" encoding=\"GB2312\"?><Order></Order>");
xmlNode = xmlDoc.SelectSingleNode("Order");
DataTable mast = ConnectSql.GetDataSet(sql_mast).Tables[0];
DataTable tab_mark = ConnectSql.GetDataSet(sql_job).Tables[0];
DataTable tab_job1 = ConnectSql.GetDataSet(sql_job1).Tables[0];
DataTable tab_job2 = ConnectSql.GetDataSet(sql_job2).Tables[0];
if (mast.Rows.Count != 1 || tab_mark.Rows.Count == 0)
{
return(null);
}
XmlElement item = null;
for (int j = 0; j < mast.Rows.Count; j++)
{
item = xmlNode as XmlElement;//xmlDoc.CreateElement("" + element + "");
item.SetAttribute("Id", mast.Rows[j]["Id"].ToString());
item.SetAttribute("RelaId", mast.Rows[j]["RelaId"].ToString());
item.SetAttribute("OrderNo", mast.Rows[j]["DoNo"].ToString());
item.SetAttribute("OrderType", mast.Rows[j]["DoType"].ToString());
item.SetAttribute("OrderDate", SafeValue.SafeDate(mast.Rows[j]["DoDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("PoNo", mast.Rows[j]["PoNo"].ToString());
item.SetAttribute("PoDate", SafeValue.SafeDate(mast.Rows[j]["PoDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("Type", mast.Rows[j]["Priority"].ToString());
item.SetAttribute("ExpectedDate", SafeValue.SafeDate(mast.Rows[j]["ExpectedDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("DoStatus", mast.Rows[j]["DoStatus"].ToString());
//from company to warehouse
if (System.Configuration.ConfigurationManager.AppSettings["WhService_ToStock"].Length > 0)
{
string partyId = System.Configuration.ConfigurationManager.AppSettings["PartyIdInStock"];
string partyName = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
//partyName += "\n" + System.Configuration.ConfigurationManager.AppSettings["CompanyAddress1"];
//partyName += "\n" + System.Configuration.ConfigurationManager.AppSettings["CompanyAddress2"];
//partyName += "\n" + System.Configuration.ConfigurationManager.AppSettings["CompanyAddress3"];
item.SetAttribute("PartyId", partyId);
item.SetAttribute("Party", partyName);
item.SetAttribute("PartyAddress", mast.Rows[j]["PartyName"].ToString() + "\n" + mast.Rows[j]["PartyAddress"].ToString());
}
else// from warehouse to company
{
item.SetAttribute("PartyId", "");
item.SetAttribute("Party", "");
item.SetAttribute("PartyAddress", "");
}
//item.SetAttribute("Party", mast.Rows[j]["PartyName"].ToString());
//item.SetAttribute("PartyPostal", mast.Rows[j]["PartyPostal"].ToString());
//item.SetAttribute("PartyCity", mast.Rows[j]["PartyCity"].ToString());
//item.SetAttribute("PartyCountry", mast.Rows[j]["PartyCountry"].ToString());
//item.SetAttribute("PartyAddress", mast.Rows[j]["PartyAddress"].ToString());
item.SetAttribute("PickFrom", mast.Rows[j]["PickFrom"].ToString());
item.SetAttribute("DeliveryTo", mast.Rows[j]["DeliveryTo"].ToString());
item.SetAttribute("Remark1", mast.Rows[j]["Remark1"].ToString());
item.SetAttribute("Remark2", mast.Rows[j]["Remark2"].ToString());
item.SetAttribute("CustomsRef", mast.Rows[j]["CustomsRef"].ToString());
item.SetAttribute("RefDate", SafeValue.SafeDate(mast.Rows[j]["RefDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("PoNo", mast.Rows[j]["PoNo"].ToString());
item.SetAttribute("PoDate", SafeValue.SafeDate(mast.Rows[j]["PoDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("PartyInvNo", mast.Rows[j]["PartyInvNo"].ToString());
item.SetAttribute("PartyInvDate", SafeValue.SafeDate(mast.Rows[j]["PartyInvDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("PermitNo", mast.Rows[j]["PermitNo"].ToString());
item.SetAttribute("PermitApprovalDate", SafeValue.SafeDate(mast.Rows[j]["PermitApprovalDate"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("PermitBy", mast.Rows[j]["PermitBy"].ToString());
item.SetAttribute("OtherPermit", mast.Rows[j]["OtherPermit"].ToString());
item.SetAttribute("ModelType", mast.Rows[j]["ModelType"].ToString());
item.SetAttribute("TptMode", mast.Rows[j]["TptMode"].ToString());
item.SetAttribute("PalletizedInd", SafeValue.SafeBool(mast.Rows[j]["PalletizedInd"], true).ToString());
item.SetAttribute("EquipNo", mast.Rows[j]["EquipNo"].ToString());
item.SetAttribute("Personnel", mast.Rows[j]["Personnel"].ToString());
#region shipment info
item.SetAttribute("Vessel", SafeValue.SafeString(mast.Rows[j]["Vessel"]));
item.SetAttribute("Voyage", SafeValue.SafeString(mast.Rows[j]["Voyage"]));
item.SetAttribute("OceanBl", SafeValue.SafeString(mast.Rows[j]["OceanBl"]));
item.SetAttribute("HouseBl", SafeValue.SafeString(mast.Rows[j]["HouseBl"]));
item.SetAttribute("Pol", SafeValue.SafeString(mast.Rows[j]["Pol"]));
item.SetAttribute("Pod", SafeValue.SafeString(mast.Rows[j]["Pod"]));
item.SetAttribute("Eta", SafeValue.SafeDate(mast.Rows[j]["Eta"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("Etd", SafeValue.SafeDate(mast.Rows[j]["Etd"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("EtaDest", SafeValue.SafeDate(mast.Rows[j]["EtaDest"], DateTime.Today).ToString("yyyy-MM-dd"));
item.SetAttribute("Carrier", SafeValue.SafeString(mast.Rows[j]["Carrier"]));
item.SetAttribute("Vehicle", SafeValue.SafeString(mast.Rows[j]["Vehicle"]));
item.SetAttribute("Coo", SafeValue.SafeString(mast.Rows[j]["Coo"]));
#endregion
#region party
//party-agent
item.SetAttribute("Agent", SafeValue.SafeString(mast.Rows[j]["Agent"]));
item.SetAttribute("AgentContact", SafeValue.SafeString(mast.Rows[j]["AgentContact"]));
item.SetAttribute("AgentTel", SafeValue.SafeString(mast.Rows[j]["AgentTel"]));
item.SetAttribute("AgentAddress", SafeValue.SafeString(mast.Rows[j]["AgentAddress"]));
item.SetAttribute("AgentZip", SafeValue.SafeString(mast.Rows[j]["AgentZip"]));
item.SetAttribute("AgentCity", SafeValue.SafeString(mast.Rows[j]["AgentCity"]));
item.SetAttribute("AgentCountry", SafeValue.SafeString(mast.Rows[j]["AgentCountry"]));
//party-notify party
item.SetAttribute("Notify", SafeValue.SafeString(mast.Rows[j]["Notify"]));
item.SetAttribute("NotifyContact", SafeValue.SafeString(mast.Rows[j]["NotifyContact"]));
item.SetAttribute("NotifyTel", SafeValue.SafeString(mast.Rows[j]["NotifyTel"]));
item.SetAttribute("NotifyAddress", SafeValue.SafeString(mast.Rows[j]["NotifyAddress"]));
item.SetAttribute("NotifyZip", SafeValue.SafeString(mast.Rows[j]["NotifyZip"]));
item.SetAttribute("NotifyCity", SafeValue.SafeString(mast.Rows[j]["NotifyCity"]));
item.SetAttribute("NotifyCountry", SafeValue.SafeString(mast.Rows[j]["NotifyCountry"]));
//party-consignee
item.SetAttribute("Cnee", SafeValue.SafeString(mast.Rows[j]["Cnee"]));
item.SetAttribute("CneeContact", SafeValue.SafeString(mast.Rows[j]["CneeContact"]));
item.SetAttribute("CneeTel", SafeValue.SafeString(mast.Rows[j]["CneeTel"]));
item.SetAttribute("CneeAddress", SafeValue.SafeString(mast.Rows[j]["CneeAddress"]));
item.SetAttribute("CneeZip", SafeValue.SafeString(mast.Rows[j]["CneeZip"]));
item.SetAttribute("CneeCity", SafeValue.SafeString(mast.Rows[j]["CneeCity"]));
item.SetAttribute("CneeCountry", SafeValue.SafeString(mast.Rows[j]["CneeCountry"]));
#endregion
#region Sku
for (int k = 0; k < tab_mark.Rows.Count; k++)
{
XmlElement cont = xmlDoc.CreateElement("Sku");
cont.SetAttribute("Id", SafeValue.SafeString(tab_mark.Rows[k]["Id"]));
cont.SetAttribute("LotNo", SafeValue.SafeString(tab_mark.Rows[k]["LotNo"]));
cont.SetAttribute("Sku", SafeValue.SafeString(tab_mark.Rows[k]["Sku"]));
cont.SetAttribute("Des", SafeValue.SafeString(tab_mark.Rows[k]["Des"]));
cont.SetAttribute("CustomsLot", SafeValue.SafeString(tab_mark.Rows[k]["CustomsLot"]));
cont.SetAttribute("BatchNo", SafeValue.SafeString(tab_mark.Rows[k]["BatchNo"]));
cont.SetAttribute("Expected", SafeValue.SafeString(tab_mark.Rows[k]["Expected"]));
cont.SetAttribute("Transit", SafeValue.SafeString(tab_mark.Rows[k]["Transit"]));
cont.SetAttribute("PackQty", SafeValue.SafeString(tab_mark.Rows[k]["PackQty"]));
cont.SetAttribute("WholeQty", SafeValue.SafeString(tab_mark.Rows[k]["WholeQty"]));
cont.SetAttribute("LooseQty", SafeValue.SafeString(tab_mark.Rows[k]["LooseQty"]));
cont.SetAttribute("QtyPackWhole", SafeValue.SafeString(tab_mark.Rows[k]["QtyPackWhole"]));
cont.SetAttribute("QtyWholeLoose", SafeValue.SafeString(tab_mark.Rows[k]["QtyWholeLoose"]));
cont.SetAttribute("QtyLooseBase", SafeValue.SafeString(tab_mark.Rows[k]["QtyLooseBase"]));
cont.SetAttribute("PackUom", SafeValue.SafeString(tab_mark.Rows[k]["PackUom"]));
cont.SetAttribute("WholeUom", SafeValue.SafeString(tab_mark.Rows[k]["WholeUom"]));
cont.SetAttribute("LooseUom", SafeValue.SafeString(tab_mark.Rows[k]["LooseUom"]));
cont.SetAttribute("BaseUom", SafeValue.SafeString(tab_mark.Rows[k]["BaseUom"]));
cont.SetAttribute("Price", SafeValue.SafeString(tab_mark.Rows[k]["Price"]));
cont.SetAttribute("Att1", SafeValue.SafeString(tab_mark.Rows[k]["Att1"]));
cont.SetAttribute("Att2", SafeValue.SafeString(tab_mark.Rows[k]["Att2"]));
cont.SetAttribute("Att3", SafeValue.SafeString(tab_mark.Rows[k]["Att3"]));
cont.SetAttribute("Att4", SafeValue.SafeString(tab_mark.Rows[k]["Att4"]));
cont.SetAttribute("Att5", SafeValue.SafeString(tab_mark.Rows[k]["Att5"]));
cont.SetAttribute("Att6", SafeValue.SafeString(tab_mark.Rows[k]["Att6"]));
cont.SetAttribute("Att7", SafeValue.SafeString(tab_mark.Rows[k]["Att7"]));
cont.SetAttribute("Att8", SafeValue.SafeString(tab_mark.Rows[k]["Att8"]));
cont.SetAttribute("Att9", SafeValue.SafeString(tab_mark.Rows[k]["Att9"]));
cont.SetAttribute("Att10", SafeValue.SafeString(tab_mark.Rows[k]["Att10"]));
item.AppendChild(cont);
}
#endregion
#region PutAway
for (int a = 0; a < tab_job1.Rows.Count; a++)
{
XmlElement cont = xmlDoc.CreateElement("PutAway");
cont.SetAttribute("Id", SafeValue.SafeString(tab_job1.Rows[a]["Id"]));
cont.SetAttribute("LotNo", SafeValue.SafeString(tab_job1.Rows[a]["LotNo"]));
cont.SetAttribute("Product", SafeValue.SafeString(tab_job1.Rows[a]["Product"]));
cont.SetAttribute("Des", SafeValue.SafeString(tab_job1.Rows[a]["Des"]));
cont.SetAttribute("PackQty", SafeValue.SafeString(tab_job1.Rows[a]["PackQty"]));
cont.SetAttribute("WholeQty", SafeValue.SafeString(tab_job1.Rows[a]["WholeQty"]));
cont.SetAttribute("LooseQty", SafeValue.SafeString(tab_job1.Rows[a]["LooseQty"]));
cont.SetAttribute("QtyPackWhole", SafeValue.SafeString(tab_mark.Rows[a]["QtyPackWhole"]));
cont.SetAttribute("QtyWholeLoose", SafeValue.SafeString(tab_mark.Rows[a]["QtyWholeLoose"]));
cont.SetAttribute("QtyLooseBase", SafeValue.SafeString(tab_mark.Rows[a]["QtyLooseBase"]));
cont.SetAttribute("PackUom", SafeValue.SafeString(tab_mark.Rows[a]["PackUom"]));
cont.SetAttribute("WholeUom", SafeValue.SafeString(tab_mark.Rows[a]["WholeUom"]));
cont.SetAttribute("LooseUom", SafeValue.SafeString(tab_mark.Rows[a]["LooseUom"]));
cont.SetAttribute("BaseUom", SafeValue.SafeString(tab_mark.Rows[a]["BaseUom"]));
cont.SetAttribute("Location", SafeValue.SafeString(tab_job1.Rows[a]["Location"]));
cont.SetAttribute("ProcessStatus", SafeValue.SafeString(tab_job1.Rows[a]["ProcessStatus"]));
cont.SetAttribute("CustomsLot", SafeValue.SafeString(tab_job1.Rows[a]["CustomsLot"]));
cont.SetAttribute("BatchNo", SafeValue.SafeString(tab_job1.Rows[a]["BatchNo"]));
cont.SetAttribute("Att1", SafeValue.SafeString(tab_job1.Rows[a]["Att1"]));
cont.SetAttribute("Att2", SafeValue.SafeString(tab_job1.Rows[a]["Att2"]));
cont.SetAttribute("Att3", SafeValue.SafeString(tab_job1.Rows[a]["Att3"]));
cont.SetAttribute("Att4", SafeValue.SafeString(tab_job1.Rows[a]["Att4"]));
cont.SetAttribute("Att5", SafeValue.SafeString(tab_job1.Rows[a]["Att5"]));
cont.SetAttribute("Att6", SafeValue.SafeString(tab_job1.Rows[a]["Att6"]));
cont.SetAttribute("Att7", SafeValue.SafeString(tab_job1.Rows[a]["Att7"]));
cont.SetAttribute("Att8", SafeValue.SafeString(tab_job1.Rows[a]["Att8"]));
cont.SetAttribute("Att9", SafeValue.SafeString(tab_job1.Rows[a]["Att9"]));
cont.SetAttribute("Att10", SafeValue.SafeString(tab_job1.Rows[a]["Att10"]));
item.AppendChild(cont);
}
#endregion
#region Container
for (int a = 0; a < tab_job2.Rows.Count; a++)
{
XmlElement cont = xmlDoc.CreateElement("Container");
cont.SetAttribute("Id", SafeValue.SafeString(tab_job2.Rows[a]["Id"]));
cont.SetAttribute("ContainerNo", SafeValue.SafeString(tab_job2.Rows[a]["ContainerNo"]));
cont.SetAttribute("ContainerType", SafeValue.SafeString(tab_job2.Rows[a]["ContainerType"]));
cont.SetAttribute("SealNo", SafeValue.SafeString(tab_job2.Rows[a]["SealNo"]));
cont.SetAttribute("Weight", SafeValue.SafeString(tab_job2.Rows[a]["Weight"]));
cont.SetAttribute("M3", SafeValue.SafeString(tab_job2.Rows[a]["M3"]));
cont.SetAttribute("Qty", SafeValue.SafeString(tab_job2.Rows[a]["Qty"]));
cont.SetAttribute("PkgType", SafeValue.SafeString(tab_job2.Rows[a]["PkgType"]));
item.AppendChild(cont);
}
#endregion
//xmlNode.AppendChild(item);
}
return(xmlDoc);
}
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);
}
private void ExportArToXml(string refNo, DataTable mast)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml("<?xml version=\"1.0\" encoding=\"GB2312\"?><AirCrossTrade></AirCrossTrade>");
XmlNode xmlNode = xmlDoc.SelectSingleNode("AirCrossTrade");
XmlElement bill = null;
for (int i = 0; i < mast.Rows.Count; i++)
{
bill = xmlDoc.CreateElement("HAWB");
bill.SetAttribute("JobNo", mast.Rows[i]["JobNo"].ToString());
bill.SetAttribute("HAWB", mast.Rows[i]["HAWB"].ToString());
string customerName = EzshipHelper.GetPartyName(mast.Rows[i]["CustomerId"].ToString());
bill.SetAttribute("Customer", customerName);
bill.SetAttribute("TsBkgRef", mast.Rows[i]["TsBkgRef"].ToString());
bill.SetAttribute("TsBkgUser", mast.Rows[i]["TsBkgUser"].ToString());
bill.SetAttribute("TsBkgTime", mast.Rows[i]["TsBkgTime"].ToString());
bill.SetAttribute("DeliveryDate", mast.Rows[i]["DeliveryDate"].ToString());
bill.SetAttribute("Weight", mast.Rows[i]["Weight"].ToString());
bill.SetAttribute("Volume", mast.Rows[i]["Volume"].ToString());
bill.SetAttribute("TsInd", mast.Rows[i]["TsInd"].ToString());
bill.SetAttribute("DoReadyInd", mast.Rows[i]["DoReadyInd"].ToString());
bill.SetAttribute("PackageType", mast.Rows[i]["PackageType"].ToString());
bill.SetAttribute("Qty", mast.Rows[i]["Qty"].ToString());
bill.SetAttribute("Remark", mast.Rows[i]["Remark"].ToString());
bill.SetAttribute("Marking", mast.Rows[i]["Marking"].ToString());
bill.SetAttribute("PermitRmk", mast.Rows[i]["PermitRmk"].ToString());
bill.SetAttribute("Transporter", mast.Rows[i]["HaulierName"].ToString());
bill.SetAttribute("CrNo", mast.Rows[i]["HaulierCrNo"].ToString());
bill.SetAttribute("Attention", mast.Rows[i]["HaulierAttention"].ToString());
bill.SetAttribute("DriverName", mast.Rows[i]["DriverName"].ToString());
bill.SetAttribute("DriverMobile", mast.Rows[i]["DriverMobile"].ToString());
bill.SetAttribute("DriverLicense", mast.Rows[i]["DriverLicense"].ToString());
bill.SetAttribute("CollectDate", mast.Rows[i]["HaulierCollectDate"].ToString());
bill.SetAttribute("VehicleNo", mast.Rows[i]["VehicleNo"].ToString());
bill.SetAttribute("VehicleType", mast.Rows[i]["VehicleType"].ToString());
bill.SetAttribute("DriverRemark", mast.Rows[i]["DriverRemark"].ToString());
bill.SetAttribute("CollectFrom", mast.Rows[i]["HaulierCollect"].ToString());
bill.SetAttribute("TruckTo", mast.Rows[i]["HaulierTruck"].ToString());
bill.SetAttribute("Instruction", mast.Rows[i]["HaulierRemark"].ToString());
bill.SetAttribute("VehicleNo", mast.Rows[i]["VehicleNo"].ToString());
bill.SetAttribute("PODBy", mast.Rows[i]["PODBy"].ToString());
bill.SetAttribute("PODTime", SafeValue.SafeDateStr(mast.Rows[i]["PODTime"].ToString()));
bill.SetAttribute("PODRemark", mast.Rows[i]["PODRemark"].ToString());
#region SeaAttachment
string sql_attachments = string.Format(@"select * from air_attachment where JobNo='{0}'", mast.Rows[i]["JobNo"].ToString());
DataTable tab_attachments = ConnectSql.GetDataSet(sql_attachments).Tables[0];
for (int j = 0; j < tab_attachments.Rows.Count; j++)
{
XmlElement att = xmlDoc.CreateElement("Attachments");
att.SetAttribute("FileName", tab_attachments.Rows[j]["FileName"].ToString());
att.SetAttribute("FileType", tab_attachments.Rows[j]["FileType"].ToString());
att.SetAttribute("FilePath", tab_attachments.Rows[j]["FilePath"].ToString());
att.SetAttribute("FileNote", tab_attachments.Rows[j]["FileNote"].ToString());
att.SetAttribute("FileDate", tab_attachments.Rows[j]["FileDate"].ToString());
att.SetAttribute("FileSize", tab_attachments.Rows[j]["FileSize"].ToString());
att.SetAttribute("FileStatus", tab_attachments.Rows[j]["FileStatus"].ToString());
bill.AppendChild(att);
}
#endregion
xmlNode.AppendChild(bill);
}
MemoryStream ms = new MemoryStream();
xmlDoc.Save(ms);
byte[] bt = ms.GetBuffer();
if (bt.Length > 0)
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("Content-Disposition", "attachement; filename=" + refNo + ".xml");
Response.ContentType = "text/xml";
Response.BinaryWrite(bt);
Response.End();
}
}
public static DataSet PrintPayable(string invN)
{
DataSet ds = new DataSet();
try
{
string strsql = string.Format(@"exec proc_PrintPayable '{0}','{1}','{2}','{3}','{4}'", invN, "", "", System.Configuration.ConfigurationManager.AppSettings["CompanyInvHeader1"] + "\n" + System.Configuration.ConfigurationManager.AppSettings["CompanyInvHeader2"], System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
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";
for (int i = 5; Detail.Rows.Count < i;)
{
Detail.Rows.Add(Detail.NewRow());
}
ds.Tables.Add(Mast);
ds.Tables.Add(Detail);
DataRelation rr = new DataRelation("", Mast.Columns["InvoiceN"], Detail.Columns["InvoiceN"]);
ds.Relations.Add(rr);
}
catch (Exception ex) { }
return(ds);
// DataSet set = new DataSet();
// decimal gstAmt = 0;
// decimal amt = 0;
// DataTable tab_mast = new DataTable("Mast");
// tab_mast.Columns.Add("InvoiceN");
// tab_mast.Columns.Add("InvDate");
// tab_mast.Columns.Add("RefNo");
// tab_mast.Columns.Add("MastType");
// tab_mast.Columns.Add("JobType");
// tab_mast.Columns.Add("Name");
// tab_mast.Columns.Add("VesVoy");
// tab_mast.Columns.Add("Pol");
// tab_mast.Columns.Add("Pod");
// tab_mast.Columns.Add("Eta");
// tab_mast.Columns.Add("Obl");
// tab_mast.Columns.Add("Des");
// tab_mast.Columns.Add("Currency");
// tab_mast.Columns.Add("TotAmt");
// tab_mast.Columns.Add("UserId");
// DataTable tab_det = new DataTable("Detail");
// tab_det.Columns.Add("InvoiceN");
// tab_det.Columns.Add("LineN");
// tab_det.Columns.Add("Des");
// tab_det.Columns.Add("Currency");
// tab_det.Columns.Add("Price");
// tab_det.Columns.Add("Qty");
// tab_det.Columns.Add("Unit");
// tab_det.Columns.Add("Rate");
// tab_det.Columns.Add("GstAmt");
// tab_det.Columns.Add("LineAmt");
// string sql_mast = string.Format(@"SELECT DocType, DocNo, DocDate, PartyTo, MastRefNo, JobRefNo, MastType, CurrencyId, ExRate, Term, Description,UserId
//FROM XAApPayable WHERE (DocNo = '{0}') AND (DocType = 'PL')", invN);
// string sql_det = string.Format(@"SELECT ChgCode, ChgDes1, ChgDes2, ChgDes3, ChgDes4, GstType, Qty, Price, Unit, Currency, ExRate, Gst, GstAmt, DocAmt, LocAmt
//FROM XAApPayableDet WHERE (DocNo = '{0}') AND (DocType = 'PL')", invN);
// DataTable mast = Manager.ORManager.GetDataSet(sql_mast).Tables[0];
// if (mast.Rows.Count == 0) return set;
// DataTable det = Manager.ORManager.GetDataSet(sql_det).Tables[0];
// int index = 0;
// for (int i = 0; i < det.Rows.Count; i++)
// {
// DataRow oldRow = det.Rows[i];
// DataRow newRow = tab_det.NewRow();
// index++;
// newRow["InvoiceN"] = invN;
// newRow["LineN"] = index.ToString();
// string des = "";
// if (SafeValue.SafeString(oldRow["ChgDes1"], "").Length > 0)
// {
// des += SafeValue.SafeString(oldRow["ChgDes1"], "");
// }
// //if (SafeValue.SafeString(oldRow["ChgDes2"], "").Length > 0)
// //{
// // des += "\n" + SafeValue.SafeString(oldRow["ChgDes2"], "");
// //}
// //if (SafeValue.SafeString(oldRow["ChgDes3"], "").Length > 0)
// //{
// // des += "\n" + SafeValue.SafeString(oldRow["ChgDes3"], "");
// //}
// //if (SafeValue.SafeString(oldRow["ChgDes4"], "").Length > 0)
// //{
// // des += "\n" + SafeValue.SafeString(oldRow["ChgDes4"], "");
// //}
// newRow["Des"] = des;
// newRow["Qty"] = SafeValue.SafeDecimal(oldRow["QTY"], 0).ToString("0.000");
// newRow["Price"] = SafeValue.SafeDecimal(oldRow["PRICE"], 0).ToString("0.000");
// newRow["Unit"] = oldRow["UNIT"];
// newRow["Currency"] = oldRow["Currency"];
// newRow["Rate"] = SafeValue.SafeDecimal(oldRow["ExRate"], 1).ToString("0.0000");
// if (SafeValue.SafeDecimal(oldRow["GstAmt"], 0) == 0)
// {
// newRow["GstAmt"] = "";
// }
// else
// {
// newRow["GstAmt"] = SafeValue.SafeDecimal(oldRow["GstAmt"], 0).ToString("0.00");
// }
// newRow["LineAmt"] = SafeValue.SafeDecimal(oldRow["LocAmt"], 0).ToString("0.00");
// tab_det.Rows.Add(newRow);
// amt += SafeValue.ChinaRound(SafeValue.SafeDecimal(oldRow["LocAmt"], 0) - SafeValue.SafeDecimal(oldRow["GstAmt"], 0), 2);
// gstAmt += SafeValue.ChinaRound(SafeValue.SafeDecimal(oldRow["GstAmt"], 0), 2);
//}
//DataRow row = tab_mast.NewRow();
//row["InvoiceN"] = invN;
//row["InvDate"] = SafeValue.SafeString(mast.Rows[0]["DocDate"], "");
//row["Name"] = "";
//string partyTo = SafeValue.SafeString(mast.Rows[0]["PartyTo"], "");
//row["Name"] = EzshipHelper.GetPartyName(partyTo);
//row["Currency"] = mast.Rows[0]["CurrencyId"];
//row["Des"] = mast.Rows[0]["Description"];
//string mastNo = mast.Rows[0]["MastRefNo"].ToString();
//string jobNo = mast.Rows[0]["JobRefNo"].ToString();
//string jobType = mast.Rows[0]["MastType"].ToString();
//row["RefNo"] = jobType + "/" + mastNo + "/" + jobNo;
//string sql_job = "";
//if (jobType == "SI")
//{
// sql_job = string.Format("Select Vessel, Voyage, Eta,OblNo, Pol, Pod,JobType from SeaImportRef where RefNo='{0}'", mastNo);
//}
//else if (jobType == "SE")
// sql_job = string.Format("Select Vessel, Voyage, Eta,OblNo, Pol, Pod,JobType from SeaExportRef where RefNo='{0}'", mastNo);
//else if (jobType == "AI")
// sql_job = string.Format("Select AirlineCode1 as Vessel,AirlineName1 as Voyage,(FlightDate0+''+FlightTime0) as Eta,NvoccBlNO as OblNo,AirportName0 as Pol,AirportName1 as Pod, RefType as JobType from air_ref where RefNo='{0}'", mastNo);
//else if (jobType == "AE")
// sql_job = string.Format("Select AirlineCode1 as Vessel,AirlineName1 as Voyage,(FlightDate0+''+FlightTime0) as Eta,NvoccBlNO as OblNo,AirportName0 as Pol,AirportName1 as Pod, RefType as JobType from air_ref where RefNo='{0}'", mastNo);
//DataTable tab_job = Manager.ORManager.GetDataSet(sql_job).Tables[0];
//if (tab_job.Rows.Count > 0)
//{
// DataRow row_job = tab_job.Rows[0];
// row["VesVoy"] = SafeValue.SafeString(row_job["Vessel"], "") + " " + SafeValue.SafeString(row_job["Voyage"], "");
// row["Eta"] = SafeValue.SafeDate(row_job["Eta"], new DateTime(1900, 1, 1)).ToString("dd/MM/yyyy");
// row["Pol"] = SafeValue.SafeString(ConnectSql.ExecuteScalar("select name from XXPort where Code='" + row_job["Pol"] + "'"), row_job["Pol"].ToString());
// row["Pod"] = SafeValue.SafeString(ConnectSql.ExecuteScalar("select name from XXPort where Code='" + row_job["Pod"] + "'"), row_job["Pod"].ToString());
// row["Obl"] = row_job["OblNo"];
// row["JobType"] = row_job["JobType"];
//}
//if (jobType == "SE")
// row["MastType"] = "Export";
//else if (jobType == "SI")
// row["MastType"] = "Import";
//else if (jobType == "AI")
// row["MastType"] = "Import";
//else if (jobType == "AE")
// row["MastType"] = "Export";
//row["TotAmt"] = SafeValue.ChinaRound(amt + gstAmt, 2).ToString("0.00");
//row["UserId"] = mast.Rows[0]["UserId"];
//tab_mast.Rows.Add(row);
//set.Tables.Add(tab_mast);
//set.Tables.Add(tab_det);
//DataRelation r = new DataRelation("", tab_mast.Columns["InvoiceN"], tab_det.Columns["InvoiceN"]);
//set.Relations.Add(r);
//return set;
}
public static DataSet PrintInvoice(string invN, string docType)
{
DataSet ds = new DataSet();
try
{
string strsql = string.Format(@"exec proc_PrintInvoice '{0}','{1}','{2}','{3}','{4}'", invN, "", docType, System.Configuration.ConfigurationManager.AppSettings["CompanyInvHeader1"] + "\n" + System.Configuration.ConfigurationManager.AppSettings["CompanyInvHeader2"], System.Configuration.ConfigurationManager.AppSettings["CompanyName"].ToString());
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";
for (int i = 5; Detail.Rows.Count < i;)
{
Detail.Rows.Add(Detail.NewRow());
}
ds.Tables.Add(Mast);
ds.Tables.Add(Detail);
DataRelation r = new DataRelation("", Mast.Columns["InvoiceN"], Detail.Columns["InvoiceN"]);
ds.Relations.Add(r);
}
catch (Exception ex) { }
return(ds);
// DataSet set = new DataSet();
// decimal gstAmt = 0;
// decimal amt = 0;//
// string cust_id = "2001";//
// string address = "";
// DataTable tab_mast = new DataTable("Mast");
// DataTable tab_det = new DataTable("Detail");
// #region init column
// tab_mast.Columns.Add("BillType");
// tab_mast.Columns.Add("BillTypeStr");
// tab_mast.Columns.Add("BarCode");
// tab_mast.Columns.Add("InvoiceN");
// tab_mast.Columns.Add("InvoiceType");
// tab_mast.Columns.Add("InvDate");
// tab_mast.Columns.Add("Name");
// tab_mast.Columns.Add("Address");
// tab_mast.Columns.Add("CustId");
// tab_mast.Columns.Add("Terms");
// tab_mast.Columns.Add("VesVoy");
// tab_mast.Columns.Add("Eta");
// tab_mast.Columns.Add("Etd");
// tab_mast.Columns.Add("RefNo");
// tab_mast.Columns.Add("JobNo");
// tab_mast.Columns.Add("OblNo");
// tab_mast.Columns.Add("HblNo");
// tab_mast.Columns.Add("Pol");
// tab_mast.Columns.Add("Pod");
// tab_mast.Columns.Add("Des");
// tab_mast.Columns.Add("Qty");
// tab_mast.Columns.Add("PkgType");
// tab_mast.Columns.Add("Wt");
// tab_mast.Columns.Add("M3");
// tab_mast.Columns.Add("MoneyWords");
// tab_mast.Columns.Add("Amt");
// tab_mast.Columns.Add("TaxAmt");
// tab_mast.Columns.Add("TotAmt");
// tab_mast.Columns.Add("UserId");
// tab_mast.Columns.Add("CompanyName");
// tab_mast.Columns.Add("CompanyInvHeader");
// tab_mast.Columns.Add("Currency");
// tab_mast.Columns.Add("ExRate");
// tab_mast.Columns.Add("Shipper");
// tab_mast.Columns.Add("Cng");
// tab_mast.Columns.Add("ShipTo");
// tab_det.Columns.Add("InvoiceN");
// tab_det.Columns.Add("LineN");
// tab_det.Columns.Add("Des");
// tab_det.Columns.Add("Currency");
// tab_det.Columns.Add("Price");
// tab_det.Columns.Add("Qty");
// tab_det.Columns.Add("Unit");
// tab_det.Columns.Add("Rate");
// tab_det.Columns.Add("GstType");
// tab_det.Columns.Add("GstAmt");
// tab_det.Columns.Add("LineAmt");
// #endregion
// string sql = string.Format(@"SELECT DocType, DocNo, DocDate, PartyTo, MastRefNo, JobRefNo, MastType, CurrencyId, ExRate, Term, UserId, EntryDate, Description
// FROM XAArInvoice WHERE (DocNo = '{0}') and (DocType='{1}') ", invN, docType);
// string sql1 = string.Format(@"SELECT DocLineNo,ChgCode, ChgDes1, ChgDes2, ChgDes3, ChgDes4, GstType, Qty, Price, Unit, Currency, ExRate, Gst, GstAmt, DocAmt, LocAmt
//FROM XAArInvoiceDet WHERE (DocNo = '{0}') and (DocType='{1}') order by DocLineNo", invN, docType);
// DataTable master = ConnectSql.GetTab(sql);
// DataTable det = ConnectSql.GetTab(sql1);
// for (int i = 0; i < det.Rows.Count; i++)
// {
// DataRow oldRow = det.Rows[i];
// DataRow newRow = tab_det.NewRow();
// newRow["InvoiceN"] = invN;
// newRow["LineN"] = oldRow["DocLineNo"];
// string des = "";
// if (SafeValue.SafeString(oldRow["ChgDes1"], "").Length > 0)
// {
// des += SafeValue.SafeString(oldRow["ChgDes1"], "");
// }
// if (SafeValue.SafeString(oldRow["ChgDes2"], "").Length > 0)
// {
// des += "\n" + SafeValue.SafeString(oldRow["ChgDes2"], "");
// }
// if (SafeValue.SafeString(oldRow["ChgDes3"], "").Length > 0)
// {
// des += "\n" + SafeValue.SafeString(oldRow["ChgDes3"], "");
// }
// if (SafeValue.SafeString(oldRow["ChgDes4"], "").Length > 0)
// {
// des += "\n" + SafeValue.SafeString(oldRow["ChgDes4"], "");
// }
// newRow["Des"] = des;
// newRow["Currency"] = oldRow["Currency"];
// newRow["Qty"] = SafeValue.SafeDecimal(oldRow["QTY"], 0).ToString("0.000");
// newRow["Price"] = SafeValue.SafeDecimal(oldRow["PRICE"], 0).ToString("0.000");
// newRow["GstType"] = oldRow["GstType"];
// newRow["Unit"] = oldRow["UNIT"];
// newRow["Rate"] = SafeValue.SafeDecimal(oldRow["ExRate"], 0).ToString("0.0000");
// if (SafeValue.SafeDecimal(oldRow["GstAmt"], 0) == 0)
// {
// newRow["GstAmt"] = "";
// }
// else
// {
// newRow["GstAmt"] = SafeValue.SafeDecimal(oldRow["GstAmt"], 0).ToString("0.00");
// }
// newRow["LineAmt"] = (SafeValue.SafeDecimal(oldRow["LocAmt"], 0) - SafeValue.SafeDecimal(oldRow["GstAmt"], 0)).ToString("0.00");
// tab_det.Rows.Add(newRow);
// amt += SafeValue.ChinaRound(SafeValue.SafeDecimal(oldRow["LocAmt"], 0) - SafeValue.SafeDecimal(oldRow["GstAmt"], 0), 2);
// gstAmt += SafeValue.ChinaRound(SafeValue.SafeDecimal(oldRow["GstAmt"], 0), 2);
// }
// for (int i = 0; i < 5 - det.Rows.Count; i++)
// {
// DataRow newRow = tab_det.NewRow();
// tab_det.Rows.Add(newRow);
// }
// if (master.Rows.Count == 1)
// {
// DataRow row_Source = master.Rows[0];
// DataRow row = tab_mast.NewRow();
// row["BillType"] = "TAX INVOICE";
// row["BarCode"] = docType + "-" + invN;
// if (docType == "CN")
// {
// row["BillType"] = "CREDIT NOTE";
// row["BillTypeStr"] = "CREDIT NOTE";
// }
// else if (docType == "DN")
// {
// row["BillType"] = "DEBIT NOTE";
// row["BillTypeStr"] = "DEBIT NOTE";
// }
// row["InvoiceN"] = invN;
// row["InvoiceType"] = docType;
// row["InvDate"] = SafeValue.SafeDate(row_Source["DocDate"], new DateTime(1900, 1, 1)).ToString("dd/MM/yyyy");
// cust_id = SafeValue.SafeString(row_Source["PartyTo"], "");
// string sql_cust = "select Name,Address,Tel1,Fax1,SalesmanId from XXParty where PartyId = '" + cust_id + "'";
// string salesman = "";
// DataTable tab_cust = ConnectSql.GetTab(sql_cust);
// if (tab_cust.Rows.Count > 0)
// {
// // cust_id = tab_cust.Rows[0][0].ToString();
// address = tab_cust.Rows[0]["Address"].ToString().Trim();
// string tel = tab_cust.Rows[0]["Tel1"].ToString().Trim();
// string fax = tab_cust.Rows[0]["Fax1"].ToString().Trim();
// salesman = SafeValue.SafeString(tab_cust.Rows[0]["SalesmanId"], "");
// if (tel.Length > 0)
// {
// address += "\nTel:" + tel;
// }
// if (fax.Length > 0)
// {
// address += " Fax:" + fax;
// }
// row["Name"] = tab_cust.Rows[0]["Name"].ToString() + "[" + cust_id + "]";
// row["Address"] = address;
// }
// row["CustId"] = cust_id;
// row["Terms"] = row_Source["Term"];
// row["Des"] = row_Source["Description"];
// string mastNo = row_Source["MastRefNo"].ToString();
// string jobNo = row_Source["JobRefNo"].ToString();
// string jobType = row_Source["MastType"].ToString();
// row["RefNo"] = mastNo;
// row["JobNo"] = jobNo;
// string sql_ref = "";
// string sql_job = "";
// string sql_mkg = "";
// if (jobType == "SI")
// {
// sql_ref = string.Format(@"select OblNo,Pol,Pod,Eta,Etd,Vessel,Voyage,Weight,Volume,Qty,Packagetype,SShipperRemark,SConsigneeRemark From SeaImportRef where RefNo='{0}' ", mastNo, jobNo);
// sql_job = string.Format(@"select job.HblNo,job.Weight,job.Volume,job.Qty,job.Packagetype,job.SShipperRemark,job.SConsigneeRemark
//from SeaImport job where Job.RefNo='{0}' and job.JobNo='{1}'", mastNo, jobNo);
// sql_mkg = string.Format("SELECT top(1) Description FROM SeaImportMkg where RefNo='{0}' and JobNo='{1}' order by SequenceId", mastNo, jobNo);
// }
// else if (jobType == "SE")
// {
// sql_ref = string.Format(@"select OblNo,Pol,Pod,Eta,Etd,Vessel,Voyage,Weight,Volume,Qty,Packagetype,SShipperRemark,SConsigneeRemark From SeaExportRef where RefNo='{0}' ", mastNo, jobNo);
// sql_job = string.Format(@"select job.HblNo,job.Weight,job.Volume,job.Qty,job.Packagetype,job.SShipperRemark,job.SConsigneeRemark
//from SeaExport job where Job.RefNo='{0}' and job.JobNo='{1}'", mastNo, jobNo);
// sql_mkg = string.Format("SELECT top(1) Description FROM SeaExportMkg where RefNo='{0}' and JobNo='{1}' order by SequenceId", mastNo, jobNo);
// }
// else if (jobType == "AI")
// {
// sql_ref = string.Format("Select AirportCode0 as Vessel, AirportName0 as Voyage,(FlightDate0+' '+FlightTime0) as Eta, MAWB, NvoccBlNO as Pol,CarrierBkgNo as Pod, Currency, CurrencyRate from air_ref where RefNo='{0}'", mastNo);
// sql_job = string.Format("SELECT HAWB, Total, Unit, GrossWeight,TotalPrepaid, TotalCollect FROM air_job WHERE (RefNo = '{0}') AND (JobNo = '{1}')", mastNo, jobNo);
// sql_mkg = string.Format("SELECT Remark FROM air_costing where RefNo='{0}' and JobNo='{1}'", mastNo, jobNo);
// }
// else if (jobType == "AE")
// {
// sql_ref = string.Format("Select AirportCode0 as Vessel, AirportName0 as Voyage,(FlightDate0+' '+FlightTime0) as Eta, MAWB,NvoccBlNO as Pol,CarrierBkgNo as Pod, Currency, CurrencyRate from air_ref where RefNo='{0}'", mastNo);
// sql_job = string.Format("SELECT HAWB, Total, Unit, GrossWeight, TotalPrepaid, TotalCollect FROM air_job WHERE (RefNo = '{0}') AND (JobNo = '{1}')", mastNo, jobNo);
// sql_mkg = string.Format("SELECT Remark FROM air_costing where RefNo='{0}' and JobNo='{1}'", mastNo, jobNo);
// }
// DataTable tab_ref = Manager.ORManager.GetDataSet(sql_ref).Tables[0];
// if (tab_ref.Rows.Count > 0)
// {
// DataRow row_job = tab_ref.Rows[0];
// row["OblNo"] = SafeValue.SafeString(row_job["OblNo"], "");
// row["VesVoy"] = SafeValue.SafeString(row_job["Vessel"], "") + " " + SafeValue.SafeString(row_job["Voyage"], "");
// row["Eta"] = SafeValue.SafeDateStr(row_job["Eta"]);
// row["Etd"] = SafeValue.SafeDateStr(row_job["Eta"]);
// row["Pol"] = EzshipHelper.GetPortName(row_job["Pol"]);
// row["Pod"] = EzshipHelper.GetPortName(row_job["Pod"]);
// row["Qty"] = row_job["Qty"];
// row["PkgType"] = row_job["PackageType"];
// row["Wt"] = SafeValue.SafeDecimal(row_job["Weight"], 0).ToString("0.000");
// row["M3"] = SafeValue.SafeDecimal(row_job["Volume"], 0).ToString("0.000");
// row["Shipper"] = row_job["SShipperRemark"];
// row["Cng"] = row_job["SConsigneeRemark"];
// //row["ShipTo"] = row_job["PackageType"];
// }
// DataTable tab_job = Manager.ORManager.GetDataSet(sql_job).Tables[0];
// if (tab_job.Rows.Count > 0)
// {
// DataRow row_job = tab_job.Rows[0];
// row["Qty"] = row_job["Qty"];
// row["PkgType"] = row_job["PackageType"];
// row["Wt"] = SafeValue.SafeDecimal(row_job["Weight"], 0).ToString("0.000");
// row["M3"] = SafeValue.SafeDecimal(row_job["Volume"], 0).ToString("0.000");
// row["HblNo"] = SafeValue.SafeString(row_job["HblNo"], "");
// row["Shipper"] = row_job["SShipperRemark"];
// row["Cng"] = row_job["SConsigneeRemark"];
// //row["ShipTo"] = row_job["PackageType"];
// }
// row["Des"] = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql_mkg), "").Replace("\n", " ");
// row["Amt"] = SafeValue.ChinaRound(amt, 2).ToString("0.00");
// row["TaxAmt"] = SafeValue.ChinaRound(gstAmt, 2).ToString("0.00");
// row["TotAmt"] = SafeValue.ChinaRound(amt + gstAmt, 2).ToString("0.00");
// string currency = row_Source["CurrencyId"].ToString().ToUpper();
// decimal exRate = SafeValue.SafeDecimal(row_Source["ExRate"], 0);
// row["MoneyWords"] = "TOTAL SINGAPORE DOLLARS: " + (new NumberToMoney()).NumberToCurrency(SafeValue.ChinaRound(amt + gstAmt, 2)).ToUpper() + " ONLY";
// if (currency == "USD")
// row["MoneyWords"] = "TOTAL US DOLLARS: " + (new NumberToMoney()).NumberToCurrency(SafeValue.ChinaRound(amt + gstAmt, 2)).ToUpper() + " ONLY";
// row["Currency"] = currency;
// row["ExRate"] = exRate.ToString("0.000");
// row["UserId"] = row_Source["UserId"];
// if (salesman.Length > 2)
// {
// row["UserId"] = SafeValue.SafeString(row_Source["UserId"], "") + " / " + salesman;
// }
// row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
// row["CompanyInvHeader"] = System.Configuration.ConfigurationManager.AppSettings["CompanyInvHeader1"] + "\n" + System.Configuration.ConfigurationManager.AppSettings["CompanyInvHeader2"];
// tab_mast.Rows.Add(row);
// }
// set.Tables.Add(tab_mast);
// set.Tables.Add(tab_det);
// DataRelation r = new DataRelation("", tab_mast.Columns["InvoiceN"], tab_det.Columns["InvoiceN"]);
// set.Relations.Add(r);
// return set;
}
public static XmlDocument ExportArToXml(string docNo)
{
string sql_mast = string.Format(@"select *,(select HblNo from dbo.fun_PrintInvoice_GetPart(XaArInvoice.MastRefNo,XAArInvoice.JobRefNo,XaArInvoice.MastType)) HblNo from [dbo].[XAArInvoice] WHERE (DocNo = '{0}')", docNo);
DataTable mast = ConnectSql.GetDataSet(sql_mast).Tables[0];
if (mast.Rows.Count == 0 && docNo.Length == 0)
{
return(null);
}
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml("<?xml version=\"1.0\" encoding=\"GB2312\"?><Invoices></Invoices>");
XmlNode xmlNode = xmlDoc.SelectSingleNode("Invoices");
XmlElement bill = null;
for (int i = 0; i < mast.Rows.Count; i++)
{
bill = xmlDoc.CreateElement("Invoice");
bill.SetAttribute("DocNo", mast.Rows[i]["DocNo"].ToString());
bill.SetAttribute("DocType", mast.Rows[i]["DocType"].ToString());
bill.SetAttribute("DocDate", SafeValue.SafeDate(mast.Rows[i]["DocDate"], DateTime.Today).ToString("yyyy-MM-dd"));
string partyId = mast.Rows[i]["PartyTo"].ToString();
string partyName = SafeValue.SafeString(EzshipHelper.GetPartyName(partyId));
bill.SetAttribute("PartyTo", partyName);
bill.SetAttribute("PartyFrom", System.Configuration.ConfigurationManager.AppSettings["CompanyName"]);
bill.SetAttribute("Currency", mast.Rows[i]["CurrencyId"].ToString());
bill.SetAttribute("ExRate", SafeValue.SafeString(mast.Rows[i]["ExRate"], "0.000"));
bill.SetAttribute("DocAmt", SafeValue.SafeString(mast.Rows[i]["DocAmt"], "0.000"));
bill.SetAttribute("LocAmt", SafeValue.SafeString(mast.Rows[i]["LocAmt"], "0.000"));
bill.SetAttribute("Description", mast.Rows[i]["Description"].ToString());
//bill.SetAttribute("UserName", mast.Rows[i]["UserId"].ToString());
string refNo = mast.Rows[i]["MastRefNo"].ToString();
string mastType = mast.Rows[i]["MastType"].ToString();
string jobNo = mast.Rows[i]["JobRefNo"].ToString();
bill.SetAttribute("HblNo", mast.Rows[i]["HblNo"].ToString());
docNo = mast.Rows[i]["DocNo"].ToString();
string sql_det = string.Format(@"SELECT ChgDes1, Qty, Price, Unit, Currency, ExRate,Gst, GstAmt, DocAmt, LocAmt,LineLocAmt,ChgCode
FROM XAArInvoiceDet WHERE (DocNo = '{0}') ORDER BY DocLineNo", docNo);
DataTable tab_det = ConnectSql.GetDataSet(sql_det).Tables[0];
for (int j = 0; j < tab_det.Rows.Count; j++)
{
XmlElement item = xmlDoc.CreateElement("Item");
string qty = tab_det.Rows[j]["Qty"].ToString();
string price = tab_det.Rows[j]["Price"].ToString();
string unit = tab_det.Rows[j]["Unit"].ToString();
string lineCurrency = tab_det.Rows[j]["Currency"].ToString();
string lineExRate = tab_det.Rows[j]["ExRate"].ToString();
decimal lineGst = SafeValue.SafeDecimal(tab_det.Rows[j]["Gst"], 0);
decimal lineAmt = SafeValue.SafeDecimal(tab_det.Rows[j]["LocAmt"], 0);
decimal lineGstAmt = SafeValue.SafeDecimal(tab_det.Rows[j]["GstAmt"], 0);
decimal lineLocAmt = SafeValue.SafeDecimal(tab_det.Rows[j]["LocAmt"], 0);
decimal lineTotLocAmt = SafeValue.SafeDecimal(tab_det.Rows[j]["LineLocAmt"], 0);
string lineDes = tab_det.Rows[j]["ChgDes1"].ToString();
item.SetAttribute("Qty", qty);
item.SetAttribute("Price", price);
item.SetAttribute("Unit", unit);
item.SetAttribute("Currency", lineCurrency);
item.SetAttribute("ExRate", lineExRate);
item.SetAttribute("Gst", lineGst.ToString("0.000"));
item.SetAttribute("GstAmt", lineGstAmt.ToString("0.000"));
item.SetAttribute("Amt", lineAmt.ToString("0.000"));
item.SetAttribute("LineAmt", lineLocAmt.ToString("0.000"));
item.SetAttribute("LocAmt", lineTotLocAmt.ToString("0.000"));
item.SetAttribute("ChargeCode", tab_det.Rows[j]["ChgCode"].ToString());
item.InnerText = lineDes;
bill.AppendChild(item);
}
xmlNode.AppendChild(bill);
}
return(xmlDoc);
}