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 ASPxGridView1_CustomCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomCallbackEventArgs e)
{
ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
ASPxComboBox partyTo = this.ASPxGridView1.FindEditFormTemplateControl("cmb_PartyTo") as ASPxComboBox;
ASPxTextBox otherPartyName = this.ASPxGridView1.FindEditFormTemplateControl("txt_OtherPartyName") as ASPxTextBox;
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
ASPxComboBox docCate = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocCate") as ASPxComboBox;
ASPxComboBox docType = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType") as ASPxComboBox;
ASPxDateEdit docDate = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocDt") as ASPxDateEdit;
ASPxMemo remark = this.ASPxGridView1.FindEditFormTemplateControl("txt_Remarks1") as ASPxMemo;
ASPxComboBox termId = this.ASPxGridView1.FindEditFormTemplateControl("txt_TermId") as ASPxComboBox;
ASPxTextBox docCurr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Currency") as ASPxTextBox;
ASPxSpinEdit exRate = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocExRate") as ASPxSpinEdit;
ASPxTextBox acCode = this.ASPxGridView1.FindEditFormTemplateControl("txt_AcCode") as ASPxTextBox;
ASPxComboBox acSource = this.ASPxGridView1.FindEditFormTemplateControl("txt_AcSource") as ASPxComboBox;
ASPxTextBox supplierBillNo = this.ASPxGridView1.FindEditFormTemplateControl("txt_SupplierBillNo") as ASPxTextBox;
ASPxDateEdit supplierBillDate = this.ASPxGridView1.FindEditFormTemplateControl("txt_SupplierBillDate") as ASPxDateEdit;
ASPxTextBox chqNo = this.ASPxGridView1.FindEditFormTemplateControl("txt_ChqNo") as ASPxTextBox;
//ASPxDateEdit chqDate = this.ASPxGridView1.FindEditFormTemplateControl("txt_ChqDate") as ASPxDateEdit;
string invN = docN.Text;
C2.XAApPayable inv = Manager.ORManager.GetObject(typeof(XAApPayable), SafeValue.SafeInt(oidCtr.Text, 0)) as XAApPayable;
if (null == inv)// first insert invoice
{
inv = new XAApPayable();
invN = C2Setup.GetNextNo("AP-Voucher");
inv.PartyTo = SafeValue.SafeString(partyTo.Value, "");
inv.OtherPartyName = otherPartyName.Text;
//inv.MastType = SafeValue.SafeString(docCate.Value, "");
inv.DocType = docType.Value.ToString();
inv.DocNo = invN.ToString();
inv.DocDate = docDate.Date;
inv.Term = termId.Text;
inv.Description = remark.Text;
inv.CurrencyId = docCurr.Text.ToString();
inv.ExRate = SafeValue.SafeDecimal(exRate.Value, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = acCode.Text;
inv.AcSource = acSource.Text;
inv.SupplierBillNo = supplierBillNo.Text;
inv.SupplierBillDate = new DateTime(1900, 1, 1);
inv.ChqNo = chqNo.Text;
inv.ChqDate = supplierBillDate.Date;
inv.ExportInd = "N";
inv.UserId = HttpContext.Current.User.Identity.Name;
inv.EntryDate = DateTime.Now;
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
inv.MastRefNo = "0";
inv.JobRefNo = "0";
inv.MastType = "";
DateTime d = inv.DocDate;
if (inv.ChqDate > new DateTime(2000, 1, 1))
{
d = inv.ChqDate;
}
string[] currentPeriod = EzshipHelper.GetAccPeriod(d);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
C2Setup.SetNextNo("AP-Voucher", invN);
}
catch
{
}
}
else
{
inv.PartyTo = SafeValue.SafeString(partyTo.Value, "");
inv.OtherPartyName = otherPartyName.Text;
inv.Term = termId.Text;
inv.Description = remark.Text;
inv.CurrencyId = docCurr.Text.ToString();
inv.ExRate = SafeValue.SafeDecimal(exRate.Value, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = acCode.Text;
inv.DocDate = docDate.Date;
inv.AcSource = acSource.Text;
inv.ChqNo = chqNo.Text;
inv.SupplierBillNo = supplierBillNo.Text;
inv.ChqDate = supplierBillDate.Date;
DateTime d = inv.DocDate;
if (inv.ChqDate > new DateTime(2000, 1, 1))
{
d = inv.ChqDate;
}
string[] currentPeriod = EzshipHelper.GetAccPeriod(d);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
try
{
Manager.ORManager.StartTracking(inv, InitialState.Updated);
Manager.ORManager.PersistChanges(inv);
UpdateMaster(inv.SequenceId.ToString());
}
catch
{
}
}
Session["VoEditWhere"] = "SequenceId=" + inv.SequenceId;
this.dsApPayable.FilterExpression = Session["VoEditWhere"].ToString();
if (this.ASPxGridView1.GetRow(0) != null)
{
this.ASPxGridView1.StartEdit(0);
}
}
protected void ASPxGridView1_CustomCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomCallbackEventArgs e)
{
ASPxTextBox oidCtr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
ASPxComboBox partyTo = this.ASPxGridView1.FindEditFormTemplateControl("cmb_PartyTo") as ASPxComboBox;
ASPxTextBox docN = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocNo") as ASPxTextBox;
ASPxComboBox docType = this.ASPxGridView1.FindEditFormTemplateControl("cbo_DocType") as ASPxComboBox;
ASPxMemo remark = this.ASPxGridView1.FindEditFormTemplateControl("txt_Remarks1") as ASPxMemo;
ASPxComboBox termId = this.ASPxGridView1.FindEditFormTemplateControl("txt_TermId") as ASPxComboBox;
ASPxTextBox docCurr = this.ASPxGridView1.FindEditFormTemplateControl("txt_Currency") as ASPxTextBox;
ASPxSpinEdit exRate = this.ASPxGridView1.FindEditFormTemplateControl("txt_DocExRate") as ASPxSpinEdit;
ASPxTextBox acCode = this.ASPxGridView1.FindEditFormTemplateControl("txt_AcCode") as ASPxTextBox;
ASPxTextBox acSource = this.ASPxGridView1.FindEditFormTemplateControl("txt_AcSource") as ASPxTextBox;
ASPxTextBox supplierBillNo = this.ASPxGridView1.FindEditFormTemplateControl("txt_SupplierBillNo") as ASPxTextBox;
ASPxDateEdit supplierBillDate = this.ASPxGridView1.FindEditFormTemplateControl("txt_SupplierBillDate") as ASPxDateEdit;
ASPxDateEdit eta = this.ASPxGridView1.FindEditFormTemplateControl("txt_Eta") as ASPxDateEdit;
string invN = docN.Text;
C2.XAApPayable inv = Manager.ORManager.GetObject(typeof(XAApPayable), SafeValue.SafeInt(oidCtr.Text, 0)) as XAApPayable;
bool isNew = false;
if (invN.Length < 1) // first insert invoice
{
if (supplierBillNo.Text.Trim().Length > 0)
{
string sqlCnt = string.Format("select DocNo from XaApPayable where SupplierBillNo='{0}' and PartyTo='{1}' and DocType='{2}' AND CancelInd='N'", supplierBillNo.Text.Trim(), SafeValue.SafeString(partyTo.Value, ""), docType.Value.ToString());
string billNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sqlCnt), "");
if (billNo.Length > 0)
{
throw new Exception(string.Format("Have this Supplier Bill No In {0}({1})", billNo, docType.Value.ToString()));
return;
}
}
isNew = true;
inv = new XAApPayable();
//invN = C2Setup.GetNextNo("AP-PAYABLE");
string counterType = "AP-PAYABLE";
invN = C2Setup.GetNextNo(inv.DocType, counterType, supplierBillDate.Date);
inv.PartyTo = SafeValue.SafeString(partyTo.Value, "");
inv.MastType = ""; //SafeValue.SafeString(docCate.Value, "");
inv.DocType = docType.Value.ToString();
inv.DocNo = invN.ToString();
inv.Term = termId.Text;
inv.Description = remark.Text;
inv.CurrencyId = docCurr.Text.ToString();
inv.ExRate = SafeValue.SafeDecimal(exRate.Value, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = EzshipHelper.GetAccApCode(inv.PartyTo, inv.CurrencyId);
inv.AcSource = acSource.Text;
inv.ExportInd = "N";
inv.UserId = HttpContext.Current.User.Identity.Name;
inv.EntryDate = DateTime.Now;
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
inv.SupplierBillNo = supplierBillNo.Text;
inv.SupplierBillDate = SafeValue.SafeDate(supplierBillDate.Date, DateTime.Today);
inv.DocDate = inv.SupplierBillDate;
inv.ChqNo = "";
inv.ChqDate = new DateTime(1900, 1, 1);
inv.MastRefNo = "0";
inv.JobRefNo = "0";
inv.MastType = "";
inv.Eta = eta.Date;
string[] currentPeriod = EzshipHelper.GetAccPeriod(inv.DocDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
//C2Setup.SetNextNo("AP-PAYABLE", invN);
C2Setup.SetNextNo("", counterType, invN, inv.DocDate);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ex.StackTrace);
}
}
else
{
if (supplierBillNo.Text.Trim().Length > 0)
{
string sqlCnt = string.Format("select DocNo from XaApPayable where SupplierBillNo='{0}' and PartyTo='{1}' and DocType='{2}' and DocNo!='{3}' AND CancelInd='N'", supplierBillNo.Text.Trim(), SafeValue.SafeString(partyTo.Value, ""), docType.Value.ToString(), inv.DocNo);
string billNo = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sqlCnt), "");
if (billNo.Length > 0)
{
throw new Exception(string.Format("Have this Supplier Bill No In {0}({1})", billNo, docType.Value.ToString()));
return;
}
}
inv.PartyTo = SafeValue.SafeString(partyTo.Value, "");
inv.Term = termId.Text;
inv.Description = remark.Text;
inv.CurrencyId = docCurr.Text.ToString();
inv.ExRate = SafeValue.SafeDecimal(exRate.Value, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = EzshipHelper.GetAccApCode(inv.PartyTo, inv.CurrencyId);
inv.AcSource = acSource.Text;
inv.SupplierBillNo = supplierBillNo.Text;
inv.SupplierBillDate = SafeValue.SafeDate(supplierBillDate.Date, DateTime.Now);
inv.DocDate = inv.SupplierBillDate;
inv.Eta = eta.Date;
string[] currentPeriod = EzshipHelper.GetAccPeriod(inv.DocDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
try
{
Manager.ORManager.StartTracking(inv, InitialState.Updated);
Manager.ORManager.PersistChanges(inv);
UpdateMaster(inv.SequenceId.ToString());
}
catch
{
}
}
Session["PvEditWhere"] = "SequenceId=" + inv.SequenceId;
this.dsApPayable.FilterExpression = Session["PvEditWhere"].ToString();
if (isNew)
{
if (this.ASPxGridView1.GetRow(0) != null)
{
this.ASPxGridView1.StartEdit(0);
}
}
}
private string InsertPl(string mastType, string soNo, string doNo, string type)
{
string sql = string.Format(@"select mast.PartyId,p.termid as Term
,det.ProductCode as Sku,det.LotNo,det.Qty1 as Qty,det.Price,mast.Currency,mast.ExRate,det.Gst
,det.Des1
from wh_transDet det inner join wh_trans mast on mast.DoNO=det.DoNo and mast.DoType=det.DoType
left join xxparty p on p.partyid=mast.partyid
where mast.DoNO='{0}' ", soNo);
if (doNo.Length > 0)
{
sql = string.Format(@"select mast.PartyId,p.termid as Term
,det.ProductCode as Sku,det.LotNo,det.Qty1 as Qty,tsDet.Price,tsMast.Currency,tsMast.ExRate,tsDet.Gst
,det.Des1
from wh_DoDet det inner join wh_do mast on mast.DoNO=det.DoNo and mast.DoType=det.DoType
left join xxparty p on p.partyid=mast.partyid
left join wh_transdet tsDet on tsDet.ProductCode=det.ProductCode and tsDet.LotNo=det.LotNo
left join wh_trans tsMast on tsMast.doNO=tsDet.DoNo and tsMast.DoType=tsDet.DoType
where mast.DoNO='{1}' and tsDet.DoNO='{0}' ", soNo, doNo);
}
string invN = "";
int invId = 0;
DataTable tab = ConnectSql.GetTab(sql);
for (int i = 0; i < tab.Rows.Count; i++)
{
if (i == 0)
{
string partyId = SafeValue.SafeString(tab.Rows[0]["PartyId"]);
string term = SafeValue.SafeString(tab.Rows[0]["Term"]);
#region invoice mast
string counterType = "";
if (type == "PL")
{
counterType = "AP-PAYABLE";
}
else
{
counterType = "AP-Voucher";
}
C2.XAApPayable inv = new C2.XAApPayable();
inv.DocDate = DateTime.Today;
invN = C2Setup.GetNextNo("", counterType, inv.DocDate);
inv.PartyTo = partyId;
inv.DocType = type;
inv.DocNo = invN.ToString();
string[] currentPeriod = EzshipHelper.GetAccPeriod(inv.DocDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
inv.Term = term;
//
int dueDay = SafeValue.SafeInt(inv.Term.ToUpper().Replace("DAYS", "").Trim(), 0);
inv.Description = "";
inv.CurrencyId = System.Configuration.ConfigurationManager.AppSettings["Currency"];
inv.ExRate = 1;
inv.AcCode = EzshipHelper.GetAccArCode(inv.PartyTo, inv.CurrencyId);
inv.AcSource = "CR";
inv.MastType = mastType;
inv.MastRefNo = soNo;
inv.JobRefNo = doNo;
inv.ExportInd = "N";
inv.UserId = HttpContext.Current.User.Identity.Name;
inv.EntryDate = DateTime.Now;
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
C2Setup.SetNextNo("", counterType, invN, inv.DocDate);
invId = inv.SequenceId;
}
catch
{
}
#endregion
if (invId < 1)
{
return("");
}
}
string sku = SafeValue.SafeString(tab.Rows[i]["Sku"]);
string des = SafeValue.SafeString(tab.Rows[i]["Des1"]);
string lotNo = SafeValue.SafeString(tab.Rows[i]["LotNo"]);
int qty = SafeValue.SafeInt(tab.Rows[i]["Qty"], 0);
decimal price = SafeValue.SafeDecimal(tab.Rows[i]["Price"], 0);
string cur = SafeValue.SafeString(tab.Rows[i]["Currency"]);
decimal exRate = SafeValue.SafeDecimal(tab.Rows[i]["ExRate"], 0);
decimal gst = SafeValue.SafeDecimal(tab.Rows[i]["Gst"], 0);
InsertPl_Det(invId, invN, type, i + 1, sku, lotNo, des, qty, price, cur, exRate, gst, soNo, doNo, mastType);
}
if (invId > 0)
{
UpdateMaster1(invId);
}
return(invN);
}
public static string EdiFile(string filePath)
{
XmlDocument xmlDoc = new XmlDocument();
string docNo = "";
string docType = "";
string partyId = "";
if (true)
{
xmlDoc.Load(filePath);
XmlNodeList nodeList = xmlDoc.SelectSingleNode("Invoices").ChildNodes;
foreach (XmlNode xn in nodeList)
{
XmlElement elem = (XmlElement)xn;
C2.XAApPayable ap = null;
string refType = "";
string runType = "AP-PAYABLE";
if (ap == null)
{
ap = new C2.XAApPayable();
docNo = C2Setup.GetNextNo(refType, runType, DateTime.Now);
docType = elem.GetAttribute("DocType");
if (docType == "CN")
{
docType = "SC";
}
else if (docType == "DN")
{
docType = "SD";
}
else
{
docType = "PL";
}
string des = "";
string supplierBillNo = elem.GetAttribute("DocNo");
string hblNo = elem.GetAttribute("HblNo");
string term = elem.GetAttribute("Term");
if (term.Length == 0)
{
term = "CASH";
}
string currency = elem.GetAttribute("Currency");
DateTime supplierBillDate = SafeValue.SafeDate(elem.GetAttribute("DocDate"), DateTime.Today);
decimal exRate = SafeValue.SafeDecimal(elem.GetAttribute("ExRate"), 0);
decimal docAmt = SafeValue.SafeDecimal(elem.GetAttribute("DocAmt"), 0);
decimal locAmt = SafeValue.SafeDecimal(elem.GetAttribute("LocAmt"), 0);
ap.DocNo = docNo;
ap.DocType = docType;
ap.DocDate = DateTime.Today;
ap.PartyTo = partyId;
ap.SupplierBillDate = supplierBillDate;
ap.SupplierBillNo = supplierBillNo;
ap.Term = term;
ap.ExRate = exRate;
ap.CurrencyId = currency;
string[] acPeriod = EzshipHelper.GetAccPeriod(DateTime.Today);
ap.AcYear = SafeValue.SafeInt(acPeriod[0], DateTime.Today.Year);
ap.AcPeriod = SafeValue.SafeInt(acPeriod[1], DateTime.Today.Month);
ap.AcCode = getAcCodeByPartyId(partyId, currency);
ap.AcSource = "CR";
if (docType == "SC")
{
ap.AcSource = "DB";
}
ap.Description = des;
ap.DocAmt = docAmt;
ap.LocAmt = locAmt;
ap.BalanceAmt = docAmt;
string[] arr = getRefNoByHbl(hblNo);
ap.MastRefNo = arr[0];
ap.JobRefNo = arr[1];
ap.MastType = arr[2];
ap.CancelDate = new DateTime(1900, 1, 1);
ap.CancelInd = "N";
ap.ChqDate = new DateTime(1900, 1, 1);
ap.ChqNo = "";
ap.ExportInd = "N";
ap.UserId = EzshipHelper.GetUserName();
ap.EntryDate = DateTime.Now;
Manager.ORManager.StartTracking(ap, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(ap);
C2Setup.SetNextNo(refType, runType, ap.DocNo, ap.DocDate);
XmlNodeList nodeListJob = elem.ChildNodes;
int i = 1;
foreach (XmlNode xnJob in nodeListJob)
{
XmlElement elemDet = (XmlElement)xnJob;
string chgCode = elemDet.GetAttribute("ChargeCode");
string des1 = elem.InnerText;
string currency_det = elem.GetAttribute("Currency");
decimal exRate_det = SafeValue.SafeDecimal(elem.GetAttribute("ExRate"), 0);
decimal qty = SafeValue.SafeDecimal(elem.GetAttribute("Qty"), 0);
decimal price = SafeValue.SafeDecimal(elem.GetAttribute("Price"), 0);
string unit = elem.GetAttribute("Unit").Trim();
decimal gst = SafeValue.SafeDecimal(elem.GetAttribute("Gst"), 0);
decimal gstAmt = SafeValue.SafeDecimal(elem.GetAttribute("GstAmt"), 0);
decimal docAmt_det = SafeValue.SafeDecimal(elem.GetAttribute("Amt"), 0);
decimal locAmt_det = SafeValue.SafeDecimal(elem.GetAttribute("LocAmt"), 0);
decimal lineLocAmt_det = SafeValue.SafeDecimal(elem.GetAttribute("LineAmt"), 0);
C2.XAApPayableDet det = new XAApPayableDet();
det.AcCode = getAcCodeByChgCode(chgCode);
det.AcSource = "DB";
if (ap.AcSource == "DB")
{
det.AcSource = "CR";
}
det.ChgCode = chgCode;
det.ChgDes1 = des1;
det.ChgDes2 = " ";
det.ChgDes3 = " ";
det.ChgDes4 = " ";
det.Currency = currency_det;
det.DocAmt = docAmt_det;
det.DocId = ap.SequenceId;
det.DocLineNo = i;
det.DocNo = ap.DocNo;
det.DocType = ap.DocType;
det.ExRate = exRate_det;
det.Gst = gst;
det.GstAmt = gstAmt;
det.GstType = "Z";
if (det.Gst > 0)
{
det.GstType = "S";
}
det.JobRefNo = ap.JobRefNo;
det.LineLocAmt = lineLocAmt_det;
det.LocAmt = locAmt_det;
det.MastRefNo = ap.MastRefNo;
det.MastType = ap.MastType;
det.SplitType = "WtM3";
det.Price = price;
det.Qty = qty;
det.SplitType = "";
det.Unit = unit;
if (det.Unit.Length == 0)
{
det.Unit = " ";
}
Manager.ORManager.StartTracking(det, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(det);
}
}
}
}
return("DocType=" + docType + " , " + "DocNo=" + docNo);
}
protected void grid_WhPo_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string s = e.Parameters;
ASPxPageControl pageControl = this.grid_WhPo.FindEditFormTemplateControl("pageControl") as ASPxPageControl;
ASPxTextBox txt_Id = pageControl.FindControl("txt_Id") as ASPxTextBox;
ASPxTextBox poNo = pageControl.FindControl("txt_PoNo") as ASPxTextBox;
string sql = "select Count(*) from wh_PODet where PoNo='" + SafeValue.SafeString(poNo.Text) + "' and (StatusCode='Draft' or StatusCode='Waiting')";
int cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (cnt == 0)
{
if (s == "CloseJob")
{
#region close job
ASPxLabel closeIndStr = pageControl.FindControl("lb_JobStatus") as ASPxLabel;
sql = "select StatusCode from wh_PO where PoNo='" + poNo.Text + "'";
string closeInd = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "USE");//
if (closeInd == "CLS")
{
sql = string.Format("update wh_PO set StatusCode='USE',UpdateBy='{1}',UpdateDateTime='{2}' where PoNo='{0}'", poNo.Text, EzshipHelper.GetUserName(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
e.Result = "Success";
}
else
{
e.Result = "Fail";
}
}
else
{
sql = string.Format("update wh_PO set StatusCode='CLS',UpdateBy='{1}',UpdateDateTime='{2}' where PoNo='{0}'", poNo.Text, EzshipHelper.GetUserName(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
e.Result = "Success";
}
else
{
e.Result = "Fail";
}
}
#endregion
}
}
else
{
e.Result = "NotClose";
}
if (s == "Void")
{
#region void master
string sql_cnt = string.Format("select count(SequenceId) from XAArInvoiceDet where MastType='WH' and MastRefNo='{0}'", poNo.Text);
cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql_cnt), 0);
if (cnt > 0)
{
e.Result = "Billing";
return;
}
ASPxLabel closeIndStr = pageControl.FindControl("lb_JobStatus") as ASPxLabel;
sql = "select StatusCode from wh_PO where PoNo='" + poNo.Text + "'";
string closeInd = SafeValue.SafeString(C2.Manager.ORManager.ExecuteScalar(sql), "N");// closeIndStr.Text;
if (closeInd == "CNL")
{
sql = string.Format("update wh_PO set StatusCode='USE',UpdateBy='{1}',UpdateDateTime='{2}' where PoNo='{0}'", poNo.Text, EzshipHelper.GetUserName(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
e.Result = "Success";
}
else
{
e.Result = "Fail";
}
}
else
{
sql = string.Format("update wh_PO set StatusCode='CNL',UpdateBy='{1}',UpdateDateTime='{2}' where PoNo='{0}'", poNo.Text, EzshipHelper.GetUserName(), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
int res = Manager.ORManager.ExecuteCommand(sql);
if (res > 0)
{
e.Result = "Success";
}
else
{
e.Result = "Fail";
}
}
#endregion
}
if (s == "Invoice")
{
#region Ap Invoice
sql = string.Format(@"select count(*) from XAApPayable where MastRefNo='{0}'", poNo.Text);
int docId = 0;
cnt = SafeValue.SafeInt(C2.Manager.ORManager.ExecuteScalar(sql), 0);
if (cnt == 0)
{
ASPxTextBox partyTo = pageControl.FindControl("txt_PartyRefNo") as ASPxTextBox;
ASPxDateEdit poDate = pageControl.FindControl("txt_PoDate") as ASPxDateEdit;
string invN = "";
C2.XAApPayable inv = null;
bool isNew = false;
if (invN.Length < 1)// first insert invoice
{
isNew = true;
inv = new XAApPayable();
inv.SupplierBillDate = SafeValue.SafeDate(poDate.Date, DateTime.Today);
inv.DocDate = inv.SupplierBillDate;
inv.DocType = "PL";
invN = C2Setup.GetNextNo(inv.DocType, "AP-PAYABLE", inv.DocDate);
inv.PartyTo = SafeValue.SafeString(partyTo.Value, "");
inv.DocNo = invN.ToString();
inv.Term = "CASH";
inv.Description = "";
inv.CurrencyId = System.Configuration.ConfigurationManager.AppSettings["Currency"];
inv.ExRate = 1;
inv.AcCode = EzshipHelper.GetAccApCode(inv.PartyTo, inv.CurrencyId);
inv.AcSource = "CR";
inv.ExportInd = "N";
inv.UserId = HttpContext.Current.User.Identity.Name;
inv.EntryDate = DateTime.Now;
inv.CancelDate = new DateTime(1900, 1, 1);
inv.CancelInd = "N";
inv.ChqNo = "";
inv.ChqDate = new DateTime(1900, 1, 1);
inv.MastRefNo = poNo.Text;
inv.JobRefNo = "0";
inv.MastType = "WH";
inv.Eta = DateTime.Now;
string[] currentPeriod = EzshipHelper.GetAccPeriod(inv.DocDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], inv.DocDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], inv.DocDate.Month);
try
{
C2.Manager.ORManager.StartTracking(inv, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv);
C2Setup.SetNextNo(inv.DocType, "AP-PAYABLE", invN, inv.DocDate);
docId = inv.SequenceId;
e.Result = "Success";
}
catch
{
}
}
sql = "select * from wh_PODet where PoNo='" + poNo.Text + "'";
DataTable dt = C2.Manager.ORManager.GetDataSet(sql).Tables[0];
SavePayableDet(dt, inv.DocNo, docId);
}
else
{
DataTable tab = C2.Manager.ORManager.GetDataSet(string.Format(@"select Id from wh_PODet where PoNo='{0}'", poNo.Text)).Tables[0];
int id = 0;
C2.XAApPayableDet inv = null;
if (tab.Rows.Count > 0)
{
for (int i = 0; i < tab.Rows.Count; i++)
{
id = SafeValue.SafeInt(tab.Rows[i]["Id"], 0);
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(XAApPayableDet), "POlineId='" + id + "'");
inv = C2.Manager.ORManager.GetObject(query) as XAApPayableDet;
if (inv == null)
{
SavePayableDet(tab, inv.DocNo, docId);
}
}
}
e.Result = "Success";
}
#endregion
}
}