private string AddPurchase(DataTable tab, string doNo, string partyId, string partyName, string wh)
{
string poNo = "";
string pId = "";
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhTrans), "Id='" + pId + "'");
WhTrans whTrans = C2.Manager.ORManager.GetObject(query) as WhTrans;
whTrans = new WhTrans();
poNo = C2Setup.GetNextNo("", "PurchaseOrders", DateTime.Now);
whTrans.DoNo = poNo;
whTrans.DoType = "PO";
whTrans.PartyId = partyId;
whTrans.PartyName = partyName;
whTrans.CreateBy = EzshipHelper.GetUserName();
whTrans.CreateDateTime = DateTime.Now;
whTrans.DoDate = DateTime.Now;
whTrans.ExpectedDate = DateTime.Today.AddDays(14);
whTrans.Currency = "SGD";
whTrans.DoStatus = "Draft";
whTrans.ExRate = SafeValue.SafeDecimal(1.000000);
whTrans.WareHouseId = wh;
whTrans.BlanketNo = txt_DoNo.Text;
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whTrans);
C2Setup.SetNextNo("", "PurchaseOrders", poNo, DateTime.Now);
for (int i = 0; i < tab.Rows.Count; i++)
{
string sku = SafeValue.SafeString(tab.Rows[i]["ProductCode"]);
string lotNo = SafeValue.SafeString(tab.Rows[i]["LotNo"]);
int qty = SafeValue.SafeInt(tab.Rows[i]["Qty1"], 0);
decimal price = SafeValue.SafeDecimal(tab.Rows[i]["Price"]);
string location = SafeValue.SafeString(tab.Rows[i]["LocationCode"]);
string sql = @"Insert Into Wh_TransDet(DoNo,ProductCode,DoType,Qty1,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,LocationCode)";
sql += string.Format(@"select '{0}'as DoNo, '{1}' as Sku,'PO','{2}' as Qty1,'{3}','{5}',p.UomPacking,p.UomWhole,p.UomLoose,p.UomBase,p.QtyPackingWhole,p.QtyWholeLoose,p.QtyLooseBase,'{4}' as CreateBy,getdate() as CreateDateTime,
'{4}' as UpdateBy,getdate() as UpdateDateTime
,P.att4,P.att5,P.att6,P.att7,P.att8,P.att9,p.Description,p.Att1,'{6}'
from (select '{1}' as Sku) as tab
left join ref_product p on tab.Sku=p.Code", poNo, sku, 0, price, EzshipHelper.GetUserName(), lotNo, location);
ConnectSql.ExecuteSql(sql);
}
return(poNo);
}
protected void grid_SKULine_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string s = e.Parameters;
string soNo = SafeValue.SafeString(Request.QueryString["no"].ToString());
string schId = SafeValue.SafeString(Request.QueryString["SchId"].ToString());
string userId = HttpContext.Current.User.Identity.Name;
if (s == "Save")
{
bool result = false;
string issueN = "";
string sql = string.Format(@"select * from Wh_TransDet where DoNo='{0}'", soNo);
DataTable tab = ConnectSql.GetTab(sql);
for (int i = 0; i < tab.Rows.Count; i++)
{
if (SafeValue.SafeInt(tab.Rows[i]["Qty1"], 0) == 0)
{
e.Result = "Fail,Please keyin Qty ";
return;
}
}
C2.XAArInvoice inv = null;
C2.XAArInvoice inv_Do = null;
if (tab.Rows.Count > 0)
{
//Get SO
#region Get So
bool isNew = false;
Wilson.ORMapper.OPathQuery query1 = new Wilson.ORMapper.OPathQuery(typeof(WhTrans), "DoNo='" + soNo + "'");
WhTrans so = C2.Manager.ORManager.GetObject(query1) as WhTrans;
string sql1 = string.Format("select doNo from wh_do where DoType='Out' and PoNo='{0}'", soNo);
DataTable tab_do = ConnectSql.GetTab(sql1);
string where = "(1=0";
for (int i = 0; i < tab_do.Rows.Count; i++)
{
where += string.Format(" or DoNo='{0}' ", tab_do.Rows[i][0]);
}
where += ")";
int cnt = 0;
sql = string.Format("select count(*) from wh_transDet where DoNo='{0}' and DoType='SO' and isnull(LotNo,'')='' ", soNo);
cnt = SafeValue.SafeInt(ConnectSql.ExecuteSql(sql), 0);
if (cnt > 0)
{
e.Result = "No Balance Qty or No Lot No!";
return;
}
string update = string.Format(@"update Wh_Schedule set StatusCode='Closed' where Id='{1}'", soNo, schId);
C2.Manager.ORManager.ExecuteCommand(update);
#endregion
#region Create SO Invoice
int invId = 0;
string invN = "";
inv = Manager.ORManager.GetObject(typeof(XAArInvoice), SafeValue.SafeInt(invId, 0)) as XAArInvoice;
if (inv == null)// first insert invoice
{
string counterType = "AR-IV";
inv = new XAArInvoice();
invN = C2Setup.GetNextNo("", counterType, so.DoDate);
inv.PartyTo = SafeValue.SafeString(so.PartyId, "");
inv.DocType = "IV";
inv.DocNo = invN.ToString();
inv.DocDate = so.DoDate;
string[] currentPeriod = EzshipHelper.GetAccPeriod(so.DoDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], so.DoDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], so.DoDate.Month);
inv.Term = so.PayTerm;
//
int dueDay = 0;
if (so.PayTerm != null)
{
dueDay = SafeValue.SafeInt(so.PayTerm.ToUpper().Replace("DAYS", ""), 0);
}
inv.DocDueDate = inv.DocDate.AddDays(dueDay);//SafeValue.SafeDate(dueDt.Text, DateTime.Now);
inv.Description = so.Remark;
inv.CurrencyId = so.Currency;
inv.ExRate = SafeValue.SafeDecimal(so.ExRate, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = EzshipHelper.GetAccArCode(inv.PartyTo, inv.CurrencyId);
inv.AcSource = "DB";
inv.SpecialNote = "";
inv.MastType = "WH";
inv.MastRefNo = so.DoNo;
inv.JobRefNo = "";
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);
}
catch
{
}
}
#endregion
#region Create DO
//Create DoNo
string doId = "";
Wilson.ORMapper.OPathQuery query2 = new Wilson.ORMapper.OPathQuery(typeof(WhDo), "Id='" + doId + "'");
WhDo whDo = C2.Manager.ORManager.GetObject(query2) as WhDo;
if (whDo == null)
{
whDo = new WhDo();
isNew = true;
issueN = C2Setup.GetNextNo("", "DOOUT", so.DoDate);
whDo.DoType = "OUT";
}
whDo.DoNo = issueN;
whDo.PoNo = so.DoNo;
whDo.DoDate = so.DoDate;
whDo.PoDate = so.DoDate;
whDo.StatusCode = "CLS";
whDo.PartyId = so.PartyId;
whDo.PartyName = so.PartyName;
whDo.AgentId = so.AgentId;
whDo.AgentName = so.AgentName;
whDo.AgentTel = so.AgentTel;
whDo.AgentZip = so.AgentZip;
whDo.AgentCountry = so.AgentCountry;
whDo.AgentCity = so.AgentCity;
whDo.NotifyId = so.NotifyId;
whDo.NotifyName = so.NotifyName;
whDo.WareHouseId = so.WareHouseId;
whDo.CreateBy = so.CreateBy;
whDo.CreateDateTime = so.CreateDateTime;
whDo.UpdateBy = so.UpdateBy;
whDo.UpdateDateTime = so.UpdateDateTime;
if (isNew)
{
Manager.ORManager.StartTracking(whDo, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whDo);
C2Setup.SetNextNo("", "DOOUT", issueN, DateTime.Now);
}
#endregion
#region Create DO Invoice
inv_Do = Manager.ORManager.GetObject(typeof(XAArInvoice), SafeValue.SafeInt(invId, 0)) as XAArInvoice;
if (inv_Do == null)// first insert invoice
{
string counterType = "AR-IV";
inv_Do = new XAArInvoice();
invN = C2Setup.GetNextNo("", counterType, so.DoDate);
inv_Do.PartyTo = SafeValue.SafeString(so.PartyId, "");
inv_Do.DocType = "IV";
inv_Do.DocNo = invN.ToString();
inv_Do.DocDate = so.DoDate;
string[] currentPeriod = EzshipHelper.GetAccPeriod(so.DoDate);
inv_Do.AcYear = SafeValue.SafeInt(currentPeriod[1], so.DoDate.Year);
inv_Do.AcPeriod = SafeValue.SafeInt(currentPeriod[0], so.DoDate.Month);
inv_Do.Term = so.PayTerm;
//
int dueDay = 0;
if (so.PayTerm != null)
{
dueDay = SafeValue.SafeInt(so.PayTerm.ToUpper().Replace("DAYS", ""), 0);
}
inv_Do.DocDueDate = inv.DocDate.AddDays(dueDay);//SafeValue.SafeDate(dueDt.Text, DateTime.Now);
inv_Do.Description = so.Remark;
inv_Do.CurrencyId = so.Currency;
inv_Do.ExRate = SafeValue.SafeDecimal(so.ExRate, 1);
if (inv_Do.ExRate <= 0)
{
inv_Do.ExRate = 1;
}
inv_Do.AcCode = EzshipHelper.GetAccArCode(inv.PartyTo, inv.CurrencyId);
inv_Do.AcSource = "DB";
inv_Do.SpecialNote = "";
inv_Do.MastType = "WH";
inv_Do.MastRefNo = whDo.DoNo;
inv_Do.JobRefNo = "";
inv_Do.ExportInd = "N";
inv_Do.UserId = HttpContext.Current.User.Identity.Name;
inv_Do.EntryDate = DateTime.Now;
inv_Do.CancelDate = new DateTime(1900, 1, 1);
inv_Do.CancelInd = "N";
try
{
C2.Manager.ORManager.StartTracking(inv_Do, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv_Do);
C2Setup.SetNextNo("", counterType, invN, inv_Do.DocDate);
}
catch
{
}
}
#endregion
}
#region Create Transfer
for (int i = 0; i < tab.Rows.Count; i++)
{
string product = SafeValue.SafeString(tab.Rows[i]["ProductCode"]);
string lotNo = SafeValue.SafeString(tab.Rows[i]["LotNo"]);
int qty = SafeValue.SafeInt(tab.Rows[i]["Qty1"], 0);
decimal price = SafeValue.SafeDecimal(tab.Rows[i]["Price"]);
string des1 = SafeValue.SafeString(tab.Rows[i]["Des1"]);
string location = SafeValue.SafeString(tab.Rows[i]["LocationCode"]);
sql = @"Insert Into wh_DoDet(JobStatus,DoNo, DoType,ProductCode,ExpiredDate,Price,Qty1,Qty2,Qty3,Qty4,Qty5,LotNo,BatchNo,CustomsLot,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10,Des1,Packing,[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],DoInId)";
sql += string.Format(@" select * from (select 'Picked' as JobStatus,'{1}'as DoNo, 'Out' as DoType,ProductCode,ExpiredDate,Price
,Qty1 as Qty1
,0 as Qty2
,0 as Qty3
,Qty1 as Qty4
,0 as Qty5
, LotNo,BatchNo,CustomsLot,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10,Des1,Packing,'{2}' as CreateBy,getdate() as CreateDateTime,'{2}' as UpdateBy,getdate() as UpdateDateTime,Id as DoInId from Wh_TransDet where DoNo='{0}' and ProductCode='{4}' and LotNo='{5}' and DoType='SO' and LocationCode='{3}'
) as tab_aa where qty4>0 ", soNo, issueN, userId, location, product, lotNo);
C2.Manager.ORManager.ExecuteCommand(sql);
sql = @"Insert Into Wh_DoDet2(DoNo,DoType,Product,Qty1,Qty2,Qty3,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,Location,ProcessStatus)";
sql += string.Format(@"select '{0}'as DoNo, 'OUT' as DoType,'{1}' as Sku
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
,'{3}' as Price
,'{4}' as LotNo
,ref.UomPacking as Uom1,ref.UomWhole as Uom2,ref.UomLoose as Uom3,ref.UomBase as Uom4
,ref.QtyPackingWhole as QtyPackWhole,ref.QtyWholeLoose as QtyWholeLoose,ref.QtyLooseBase as QtyLooseBase
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
,ref.Att4 as Att1,ref.Att5 as Att2,ref.att6 as Att3,ref.att7 as Att4,ref.att8 as Att5,ref.att9 as Att6,ref.Description as Des1,'' as Packing,'{6}' as Location,'Delivered' as ProcessStatus
from (select '{1}' as Sku) as tab inner join ref_product ref on ref.Code=tab.Sku", issueN, product, qty, price, lotNo, userId, location);
C2.Manager.ORManager.ExecuteCommand(sql);
InsertInv_Det(inv.SequenceId, inv.DocNo, i + 1, product, lotNo, des1, qty, price, inv.CurrencyId, inv.ExRate, 0, inv.MastRefNo, "", inv.MastType, "IV");
UpdateMaster(inv.SequenceId);
InsertInv_Det(inv_Do.SequenceId, inv_Do.DocNo, i + 1, product, lotNo, des1, qty, price, inv_Do.CurrencyId, inv_Do.ExRate, 0, inv_Do.MastRefNo, "", inv_Do.MastType, "IV");
UpdateMaster(inv_Do.SequenceId);
if (soNo.Length > 0)
{
result = true;
}
}
#endregion
if (result)
{
e.Result = issueN;
}
else
{
e.Result = "Fail,Please keyin select product ";
}
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
string s = e.Parameters;
string schId = SafeValue.SafeString(Request.QueryString["SchId"].ToString());
//string code = SafeValue.SafeString(Request.QueryString["Loc"].ToString());
string salesman = SafeValue.SafeString(Request.QueryString["Salesman"].ToString());
string location = SafeValue.SafeString(cmb_Location.Text.Trim());
string userId = HttpContext.Current.User.Identity.Name;
if (s == "Save")
{
bool result = false;
string soNo = "";
string issueN = "";
for (int i = 0; i < list.Count; i++)
{
if (list[i].qty == 0)
{
e.Result = "Fail,Please keyin Qty ";
return;
}
}
C2.XAArInvoice inv = null;
C2.XAArInvoice inv_Do = null;
if (list.Count > 0)
{
//Create SO
#region Create So
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhSchedule), "Id='" + schId + "'");
WhSchedule sch = C2.Manager.ORManager.GetObject(query) as WhSchedule;
bool isNew = false;
Wilson.ORMapper.OPathQuery query1 = new Wilson.ORMapper.OPathQuery(typeof(WhTrans), "DoNo='" + 0 + "'");
WhTrans so = C2.Manager.ORManager.GetObject(query1) as WhTrans;
if (so == null)
{
isNew = true;
so = new WhTrans();
soNo = C2Setup.GetNextNo("", "SaleOrders", DateTime.Now);
}
so.DoNo = soNo;
so.DoDate = DateTime.Now;
so.PartyId = sch.PartyId;
so.PartyName = sch.PartyName;
so.Pic = sch.PartyContact;
so.PartyAdd = sch.PartyAdd;
so.AgentId = sch.DoctorId;
so.AgentName = EzshipHelper.GetPartyName(sch.DoctorId);
so.NotifyId = sch.Patient;
so.NotifyName = GetPatientName(sch.Patient);
so.Currency = "SGD";
so.DoType = "SO";
so.DoStatus = "Draft";
so.ExRate = 1;
so.SalesId = salesman;
so.CreateBy = userId;
so.CreateDateTime = DateTime.Now;
so.UpdateBy = userId;
so.UpdateDateTime = DateTime.Now;
if (isNew)
{
Manager.ORManager.StartTracking(so, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(so);
C2Setup.SetNextNo("", "SaleOrders", soNo, DateTime.Now);
}
else
{
Manager.ORManager.StartTracking(so, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(so);
}
sch.DoNo = soNo;
string update = string.Format(@"update Wh_Schedule set DoNo='{0}',StatusCode='Finished' where Id='{1}'", soNo, schId);
C2.Manager.ORManager.ExecuteCommand(update);
#endregion
#region Create SO Invoice
int invId = 0;
string invN = "";
inv = Manager.ORManager.GetObject(typeof(XAArInvoice), SafeValue.SafeInt(invId, 0)) as XAArInvoice;
if (inv == null)// first insert invoice
{
string counterType = "AR-IV";
inv = new XAArInvoice();
invN = C2Setup.GetNextNo("", counterType, so.DoDate);
inv.PartyTo = SafeValue.SafeString(so.PartyId, "");
inv.DocType = "IV";
inv.DocNo = invN.ToString();
inv.DocDate = so.DoDate;
string[] currentPeriod = EzshipHelper.GetAccPeriod(so.DoDate);
inv.AcYear = SafeValue.SafeInt(currentPeriod[1], so.DoDate.Year);
inv.AcPeriod = SafeValue.SafeInt(currentPeriod[0], so.DoDate.Month);
inv.Term = so.PayTerm;
//
int dueDay = 0;
if (so.PayTerm != null)
{
dueDay = SafeValue.SafeInt(so.PayTerm.ToUpper().Replace("DAYS", ""), 0);
}
inv.DocDueDate = inv.DocDate.AddDays(dueDay);//SafeValue.SafeDate(dueDt.Text, DateTime.Now);
inv.Description = so.Remark;
inv.CurrencyId = so.Currency;
inv.ExRate = SafeValue.SafeDecimal(so.ExRate, 1);
if (inv.ExRate <= 0)
{
inv.ExRate = 1;
}
inv.AcCode = EzshipHelper.GetAccArCode(inv.PartyTo, inv.CurrencyId);
inv.AcSource = "DB";
inv.SpecialNote = "";
inv.MastType = "WH";
inv.MastRefNo = so.DoNo;
inv.JobRefNo = "";
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);
}
catch
{
}
}
#endregion
#region Create DO
//Create DoNo
string doId = "";
Wilson.ORMapper.OPathQuery query2 = new Wilson.ORMapper.OPathQuery(typeof(WhDo), "Id='" + doId + "'");
WhDo whDo = C2.Manager.ORManager.GetObject(query2) as WhDo;
if (whDo == null)
{
whDo = new WhDo();
isNew = true;
issueN = C2Setup.GetNextNo("", "DOOUT", so.DoDate);
whDo.DoType = "OUT";
}
whDo.DoNo = issueN;
whDo.PoNo = so.DoNo;
whDo.DoDate = so.DoDate;
whDo.PoDate = so.DoDate;
whDo.StatusCode = "CLS";
whDo.PartyId = so.PartyId;
whDo.PartyName = so.PartyName;
whDo.AgentId = so.AgentId;
whDo.AgentName = so.AgentName;
whDo.AgentTel = so.AgentTel;
whDo.AgentZip = so.AgentZip;
whDo.AgentCountry = so.AgentCountry;
whDo.AgentCity = so.AgentCity;
whDo.NotifyId = so.NotifyId;
whDo.NotifyName = so.NotifyName;
whDo.WareHouseId = location;
whDo.CreateBy = so.CreateBy;
whDo.CreateDateTime = so.CreateDateTime;
whDo.UpdateBy = so.UpdateBy;
whDo.UpdateDateTime = so.UpdateDateTime;
if (isNew)
{
Manager.ORManager.StartTracking(whDo, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whDo);
C2Setup.SetNextNo("", "DOOUT", issueN, DateTime.Now);
}
#endregion
#region Create DO Invoice
inv_Do = Manager.ORManager.GetObject(typeof(XAArInvoice), SafeValue.SafeInt(invId, 0)) as XAArInvoice;
if (inv_Do == null)// first insert invoice
{
string counterType = "AR-IV";
inv_Do = new XAArInvoice();
invN = C2Setup.GetNextNo("", counterType, so.DoDate);
inv_Do.PartyTo = SafeValue.SafeString(so.PartyId, "");
inv_Do.DocType = "IV";
inv_Do.DocNo = invN.ToString();
inv_Do.DocDate = so.DoDate;
string[] currentPeriod = EzshipHelper.GetAccPeriod(so.DoDate);
inv_Do.AcYear = SafeValue.SafeInt(currentPeriod[1], so.DoDate.Year);
inv_Do.AcPeriod = SafeValue.SafeInt(currentPeriod[0], so.DoDate.Month);
inv_Do.Term = so.PayTerm;
//
int dueDay = 0;
if (so.PayTerm != null)
{
dueDay = SafeValue.SafeInt(so.PayTerm.ToUpper().Replace("DAYS", ""), 0);
}
inv_Do.DocDueDate = inv.DocDate.AddDays(dueDay);//SafeValue.SafeDate(dueDt.Text, DateTime.Now);
inv_Do.Description = so.Remark;
inv_Do.CurrencyId = so.Currency;
inv_Do.ExRate = SafeValue.SafeDecimal(so.ExRate, 1);
if (inv_Do.ExRate <= 0)
{
inv_Do.ExRate = 1;
}
inv_Do.AcCode = EzshipHelper.GetAccArCode(inv.PartyTo, inv.CurrencyId);
inv_Do.AcSource = "DB";
inv_Do.SpecialNote = "";
inv_Do.MastType = "WH";
inv_Do.MastRefNo = whDo.DoNo;
inv_Do.JobRefNo = "";
inv_Do.ExportInd = "N";
inv_Do.UserId = HttpContext.Current.User.Identity.Name;
inv_Do.EntryDate = DateTime.Now;
inv_Do.CancelDate = new DateTime(1900, 1, 1);
inv_Do.CancelInd = "N";
try
{
C2.Manager.ORManager.StartTracking(inv_Do, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(inv_Do);
C2Setup.SetNextNo("", counterType, invN, inv_Do.DocDate);
}
catch
{
}
}
#endregion
}
#region Create Transfer
for (int i = 0; i < list.Count; i++)
{
string id = list[i].id;
string product = list[i].product;
string lotNo = list[i].lotNo;
int qty = list[i].qty;
decimal price = list[i].price;
//string loction = list[i].location;
string des1 = list[i].des1;
int hanQty = list[i].hanQty;
if (qty > hanQty)
{
e.Result = "Fail,Please keyin the correct qty ";
return;
}
//string salesman = list[i].salesman;
string sql = @"Insert Into Wh_TransDet(DoNo, DoType,ProductCode,Qty1,Qty2,Qty3,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,LocationCode,DocAmt)";
sql += string.Format(@"select '{0}'as DoNo, 'SO' as DoType,'{1}' as ProductCode
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
,'{3}' as Price
,'{4}' as LotNo
,ref.UomPacking as Uom1,ref.UomWhole as Uom2,ref.UomLoose as Uom3,ref.UomBase as Uom4
,ref.QtyPackingWhole as QtyPackWhole,ref.QtyWholeLoose as QtyWholeLoose,ref.QtyLooseBase as QtyLooseBase
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
,ref.Att4 as Att1,ref.Att5 as Att2,ref.att6 as Att3,ref.att7 as Att4,ref.att8 as Att5,ref.att9 as Att6,ref.Description as Des1,'{6}' as LocationCode,{2}*{3} as DocAmt
from (select '{1}' as Sku) as tab inner join ref_product ref on ref.Code=tab.Sku", soNo, product, qty, price, lotNo, EzshipHelper.GetUserName(), location);
C2.Manager.ORManager.ExecuteCommand(sql);
sql = @"Insert Into wh_DoDet(JobStatus,DoNo, DoType,ProductCode,ExpiredDate,Price,Qty1,Qty2,Qty3,Qty4,Qty5,LotNo,BatchNo,CustomsLot,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10,Des1,Packing,[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],DoInId)";
sql += string.Format(@" select * from (select 'Picked' as JobStatus,'{1}'as DoNo, 'Out' as DoType,ProductCode,ExpiredDate,Price
,Qty1 as Qty1
,0 as Qty2
,0 as Qty3
,Qty1 as Qty4
,0 as Qty5
, LotNo,BatchNo,CustomsLot,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],Att1,Att2,Att3,Att4,Att5,Att6,Att7,Att8,Att9,Att10,Des1,Packing,'{2}' as CreateBy,getdate() as CreateDateTime,'{2}' as UpdateBy,getdate() as UpdateDateTime,Id as DoInId from Wh_TransDet where DoNo='{0}' and ProductCode='{4}' and LotNo='{5}' and DoType='SO' and LocationCode='{3}'
) as tab_aa where qty4>0 ", soNo, issueN, userId, location, product, lotNo);
C2.Manager.ORManager.ExecuteCommand(sql);
sql = @"Insert Into Wh_DoDet2(DoNo,DoType,Product,Qty1,Qty2,Qty3,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,Location,ProcessStatus)";
sql += string.Format(@"select '{0}'as DoNo, 'OUT' as DoType,'{1}' as Sku
,'{2}' as Qty1
,0 as Qty2
,0 as Qty3
,'{3}' as Price
,'{4}' as LotNo
,ref.UomPacking as Uom1,ref.UomWhole as Uom2,ref.UomLoose as Uom3,ref.UomBase as Uom4
,ref.QtyPackingWhole as QtyPackWhole,ref.QtyWholeLoose as QtyWholeLoose,ref.QtyLooseBase as QtyLooseBase
,'{5}' as CreateBy,getdate() as CreateDateTime,'{5}' as UpdateBy,getdate() as UpdateDateTime
,ref.Att4 as Att1,ref.Att5 as Att2,ref.att6 as Att3,ref.att7 as Att4,ref.att8 as Att5,ref.att9 as Att6,ref.Description as Des1,'' as Packing,'{6}' as Location,'Delivered' as ProcessStatus
from (select '{1}' as Sku) as tab inner join ref_product ref on ref.Code=tab.Sku", issueN, product, qty, price, lotNo, userId, location);
C2.Manager.ORManager.ExecuteCommand(sql);
InsertInv_Det(inv.SequenceId, inv.DocNo, i + 1, product, lotNo, des1, qty, price, inv.CurrencyId, inv.ExRate, 0, inv.MastRefNo, "", inv.MastType, "IV");
UpdateMaster(inv.SequenceId);
InsertInv_Det(inv_Do.SequenceId, inv_Do.DocNo, i + 1, product, lotNo, des1, qty, price, inv_Do.CurrencyId, inv_Do.ExRate, 0, inv_Do.MastRefNo, "", inv_Do.MastType, "IV");
UpdateMaster(inv_Do.SequenceId);
if (soNo.Length > 0)
{
result = true;
}
}
#endregion
if (result)
{
e.Result = soNo;
}
else
{
e.Result = "Fail,Please keyin select product ";
}
}
}
protected void SavePrice()
{
try
{
ASPxPageControl pageControl = this.grid_Price.FindEditFormTemplateControl("pageControl_Hbl") as ASPxPageControl;
ASPxTextBox txt_Id = grid_Price.FindEditFormTemplateControl("txt_Id") as ASPxTextBox;
string pId = SafeValue.SafeString(txt_Id.Text, "");
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhTrans), "Id='" + pId + "'");
WhTrans whTrans = C2.Manager.ORManager.GetObject(query) as WhTrans;
ASPxDateEdit issueDate = grid_Price.FindEditFormTemplateControl("date_IssueDate") as ASPxDateEdit;
bool isNew = false;
//const string runType = "DOOUT";
string issueN = "";
if (whTrans == null)
{
whTrans = new WhTrans();
isNew = true;
issueN = C2Setup.GetNextNo("", "PuchasePrice", issueDate.Date);
whTrans.DoDate = issueDate.Date;
}
ASPxDateEdit doDate = grid_Price.FindEditFormTemplateControl("date_IssueDate") as ASPxDateEdit;
whTrans.DoDate = doDate.Date;
ASPxDateEdit txt_ExpectedDate = this.grid_Price.FindEditFormTemplateControl("txt_ExpectedDate") as ASPxDateEdit;
whTrans.ExpectedDate = txt_ExpectedDate.Date;
ASPxComboBox doStatus = grid_Price.FindEditFormTemplateControl("cmb_Status") as ASPxComboBox;
whTrans.DoStatus = SafeValue.SafeString(doStatus.Value);
//Main Info
ASPxButtonEdit txt_PartyId = grid_Price.FindEditFormTemplateControl("txt_PartyId") as ASPxButtonEdit;
whTrans.PartyId = txt_PartyId.Text;
ASPxTextBox txt_PartyName = grid_Price.FindEditFormTemplateControl("txt_PartyName") as ASPxTextBox;
whTrans.PartyName = txt_PartyName.Text;
if (txt_PartyName.Text == "")
{
throw new Exception("Supplier not be null!!!");
return;
}
ASPxMemo remark = grid_Price.FindEditFormTemplateControl("txt_Remark") as ASPxMemo;
whTrans.Remark = remark.Text;
string userId = HttpContext.Current.User.Identity.Name;
if (isNew)
{
whTrans.StatusCode = "USE";
whTrans.CreateBy = userId;
whTrans.CreateDateTime = DateTime.Now;
whTrans.UpdateBy = userId;
whTrans.UpdateDateTime = DateTime.Now;
whTrans.DoType = "PQ";
whTrans.DoNo = issueN;
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whTrans);
C2Setup.SetNextNo("", "PuchasePrice", issueN, issueDate.Date);
}
else
{
whTrans.UpdateBy = userId;
whTrans.UpdateDateTime = DateTime.Now;
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(whTrans);
}
Session["PriceWhere"] = "DoNo='" + whTrans.DoNo + "'";
this.dsPrice.FilterExpression = Session["PriceWhere"].ToString();
if (this.grid_Price.GetRow(0) != null)
{
this.grid_Price.StartEdit(0);
}
}
catch { }
}
private string SavePo()
{
try
{
ASPxPageControl pageControl = this.grd_Do.FindEditFormTemplateControl("pageControl") as ASPxPageControl;
ASPxDateEdit txt_Date = this.grd_Do.FindEditFormTemplateControl("txt_Date") as ASPxDateEdit;
ASPxTextBox txt_Id = grd_Do.FindEditFormTemplateControl("txt_Id") as ASPxTextBox;
string pId = SafeValue.SafeString(txt_Id.Text, "");
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhTrans), "Id='" + pId + "'");
WhTrans whTrans = C2.Manager.ORManager.GetObject(query) as WhTrans;
bool action = false;
string poNo = "";
if (whTrans == null)
{
action = true;
whTrans = new WhTrans();
poNo = C2Setup.GetNextNo("", "BlanketPO", txt_Date.Date);
}
ASPxComboBox cmb_Status = this.grd_Do.FindEditFormTemplateControl("cmb_Status") as ASPxComboBox;
whTrans.DoStatus = SafeValue.SafeString(cmb_Status.Value);
ASPxButtonEdit txt_PartyId = grd_Do.FindEditFormTemplateControl("txt_PartyId") as ASPxButtonEdit;
whTrans.PartyId = SafeValue.SafeString(txt_PartyId.Text);
whTrans.DoDate = SafeValue.SafeDate(txt_Date.Date, DateTime.Today);
ASPxDateEdit txt_ExpectedDate = this.grd_Do.FindEditFormTemplateControl("txt_ExpectedDate") as ASPxDateEdit;
whTrans.ExpectedDate = txt_ExpectedDate.Date;
ASPxTextBox txt_PartyName = grd_Do.FindEditFormTemplateControl("txt_PartyName") as ASPxTextBox;
whTrans.PartyName = txt_PartyName.Text;
ASPxMemo txt_PartyAdd = grd_Do.FindEditFormTemplateControl("txt_PartyAdd") as ASPxMemo;
whTrans.PartyAdd = txt_PartyAdd.Text;
//ASPxTextBox txt_PostalCode = grd_Do.FindEditFormTemplateControl("txt_PostalCode") as ASPxTextBox;
//whTrans.PartyPostalcode = txt_PostalCode.Text;
//ASPxButtonEdit txt_PartyCity = grd_Do.FindEditFormTemplateControl("txt_PartyCity") as ASPxButtonEdit;
//whTrans.PartyCity = txt_PartyCity.Text;
//ASPxButtonEdit txt_PartyCountry = grd_Do.FindEditFormTemplateControl("txt_PartyCountry") as ASPxButtonEdit;
//whTrans.PartyCountry = txt_PartyCountry.Text;
ASPxMemo txt_Remark1 = grd_Do.FindEditFormTemplateControl("txt_Remark1") as ASPxMemo;
whTrans.Remark1 = txt_Remark1.Text;
ASPxMemo txt_Remark2 = grd_Do.FindEditFormTemplateControl("txt_Remark2") as ASPxMemo;
whTrans.Remark2 = txt_Remark2.Text;
ASPxMemo txt_CollectFrom = grd_Do.FindEditFormTemplateControl("txt_CollectFrom") as ASPxMemo;
whTrans.CollectFrom = txt_CollectFrom.Text;
ASPxMemo txt_DeliveryTo = grd_Do.FindEditFormTemplateControl("txt_DeliveryTo") as ASPxMemo;
whTrans.DeliveryTo = txt_DeliveryTo.Text;
ASPxButtonEdit txt_WareHouseId = grd_Do.FindEditFormTemplateControl("txt_WareHouseId") as ASPxButtonEdit;
whTrans.WareHouseId = txt_WareHouseId.Text;
ASPxTextBox pic = grd_Do.FindEditFormTemplateControl("txt_Pic") as ASPxTextBox;
whTrans.Pic = pic.Text;
ASPxButtonEdit currency = grd_Do.FindEditFormTemplateControl("txt_Currency") as ASPxButtonEdit;
whTrans.Currency = currency.Text;
ASPxSpinEdit exRate = grd_Do.FindEditFormTemplateControl("spin_ExRate") as ASPxSpinEdit;
whTrans.ExRate = SafeValue.SafeDecimal(exRate.Value, 1);
ASPxComboBox payTerm = grd_Do.FindEditFormTemplateControl("cmb_PayTerm") as ASPxComboBox;
whTrans.PayTerm = payTerm.Text;
ASPxComboBox incoTerm = grd_Do.FindEditFormTemplateControl("cmb_IncoTerm") as ASPxComboBox;
whTrans.IncoTerm = incoTerm.Text;
ASPxTextBox txt_MasterDocNo = grd_Do.FindEditFormTemplateControl("txt_MasterDocNo") as ASPxTextBox;
whTrans.MasterDocNo = txt_MasterDocNo.Text;
ASPxComboBox cmb_OrderType = grd_Do.FindEditFormTemplateControl("cmb_OrderType") as ASPxComboBox;
whTrans.OrderType = cmb_OrderType.Text;
//shipment info
ASPxTextBox txt_Vessel = pageControl.FindControl("txt_Vessel") as ASPxTextBox;
whTrans.Vessel = txt_Vessel.Text;
ASPxTextBox txt_Voyage = pageControl.FindControl("txt_Voyage") as ASPxTextBox;
whTrans.Voyage = txt_Voyage.Text;
ASPxDateEdit date_Etd = pageControl.FindControl("date_Etd") as ASPxDateEdit;
whTrans.Etd = date_Etd.Date;
ASPxButtonEdit txt_Pol = pageControl.FindControl("txt_Pol") as ASPxButtonEdit;
whTrans.Pol = txt_Pol.Text;
ASPxButtonEdit txt_Pod = pageControl.FindControl("txt_Pod") as ASPxButtonEdit;
whTrans.Pod = txt_Pod.Text;
ASPxDateEdit date_Eta = pageControl.FindControl("date_Eta") as ASPxDateEdit;
whTrans.Eta = date_Eta.Date;
ASPxTextBox txt_OceanBl = pageControl.FindControl("txt_OceanBl") as ASPxTextBox;
whTrans.Obl = txt_OceanBl.Text;
ASPxTextBox txt_HouseBl = pageControl.FindControl("txt_HouseBl") as ASPxTextBox;
whTrans.Hbl = txt_HouseBl.Text;
ASPxDateEdit date_EtaDest = pageControl.FindControl("date_EtaDest") as ASPxDateEdit;
whTrans.EtaDest = date_EtaDest.Date;
ASPxTextBox txt_VehicleNo = pageControl.FindControl("txt_VehicleNo") as ASPxTextBox;
whTrans.Vehicle = txt_VehicleNo.Text;
ASPxTextBox txt_COO = pageControl.FindControl("txt_COO") as ASPxTextBox;
whTrans.Coo = txt_COO.Text;
ASPxButtonEdit txt_Carrier = pageControl.FindControl("txt_Carrier") as ASPxButtonEdit;
whTrans.Carrier = txt_Carrier.Text;
//party
ASPxButtonEdit txt_AgentId = pageControl.FindControl("txt_AgentId") as ASPxButtonEdit;
whTrans.AgentId = txt_AgentId.Text;
ASPxTextBox txt_AgentName = pageControl.FindControl("txt_AgentName") as ASPxTextBox;
whTrans.AgentName = txt_AgentName.Text;
ASPxTextBox txt_AgentZip = pageControl.FindControl("txt_AgentZip") as ASPxTextBox;
whTrans.AgentZip = txt_AgentZip.Text;
ASPxMemo txt_AgentAdd = pageControl.FindControl("txt_AgentAdd") as ASPxMemo;
whTrans.AgentAdd = txt_AgentAdd.Text;
ASPxTextBox txt_AgentTel = pageControl.FindControl("txt_AgentTel") as ASPxTextBox;
whTrans.AgentTel = txt_AgentTel.Text;
ASPxTextBox txt_AgentContact = pageControl.FindControl("txt_AgentContact") as ASPxTextBox;
whTrans.AgentContact = txt_AgentContact.Text;
ASPxButtonEdit txt_AgentCountry = pageControl.FindControl("txt_AgentCountry") as ASPxButtonEdit;
whTrans.AgentCountry = txt_AgentCountry.Text;
ASPxButtonEdit txt_AgentCity = pageControl.FindControl("txt_AgentCity") as ASPxButtonEdit;
whTrans.AgentCity = txt_AgentCity.Text;
ASPxButtonEdit txt_NotifyId = pageControl.FindControl("txt_NotifyId") as ASPxButtonEdit;
whTrans.NotifyId = txt_NotifyId.Text;
ASPxTextBox txt_NotifyName = pageControl.FindControl("txt_NotifyName") as ASPxTextBox;
whTrans.NotifyName = txt_NotifyName.Text;
ASPxTextBox txt_NotifyZip = pageControl.FindControl("txt_NotifyZip") as ASPxTextBox;
whTrans.NotifyZip = txt_NotifyZip.Text;
ASPxMemo txt_NotifyAdd = pageControl.FindControl("txt_NotifyAdd") as ASPxMemo;
whTrans.NotifyAdd = txt_NotifyAdd.Text;
ASPxTextBox txt_NotifyTel = pageControl.FindControl("txt_NotifyTel") as ASPxTextBox;
whTrans.NotifyTel = txt_NotifyTel.Text;
ASPxTextBox txt_NotifyContact = pageControl.FindControl("txt_NotifyContact") as ASPxTextBox;
whTrans.NotifyContact = txt_NotifyContact.Text;
ASPxButtonEdit txt_NotifyCountry = pageControl.FindControl("txt_NotifyCountry") as ASPxButtonEdit;
whTrans.NotifyCountry = txt_NotifyCountry.Text;
ASPxButtonEdit txt_NotifyCity = pageControl.FindControl("txt_NotifyCity") as ASPxButtonEdit;
whTrans.NotifyCity = txt_NotifyCity.Text;
ASPxButtonEdit txt_ConsigneeId = pageControl.FindControl("txt_ConsigneeId") as ASPxButtonEdit;
whTrans.ConsigneeId = txt_ConsigneeId.Text;
ASPxTextBox txt_ConsigneeName = pageControl.FindControl("txt_ConsigneeName") as ASPxTextBox;
whTrans.ConsigneeName = txt_ConsigneeName.Text;
ASPxTextBox txt_ConsigneeZip = pageControl.FindControl("txt_ConsigneeZip") as ASPxTextBox;
whTrans.ConsigneeZip = txt_ConsigneeZip.Text;
ASPxMemo txt_ConsigneeAdd = pageControl.FindControl("txt_ConsigneeAdd") as ASPxMemo;
whTrans.ConsigneeAdd = txt_ConsigneeAdd.Text;
ASPxTextBox txt_ConsigneeTel = pageControl.FindControl("txt_ConsigneeTel") as ASPxTextBox;
whTrans.ConsigneeTel = txt_ConsigneeTel.Text;
ASPxTextBox txt_ConsigneeContact = pageControl.FindControl("txt_ConsigneeContact") as ASPxTextBox;
whTrans.ConsigneeContact = txt_ConsigneeContact.Text;
ASPxButtonEdit txt_ConsigneeCountry = pageControl.FindControl("txt_ConsigneeCountry") as ASPxButtonEdit;
whTrans.ConsigneeCountry = txt_ConsigneeCountry.Text;
ASPxButtonEdit txt_ConsigneeCity = pageControl.FindControl("txt_ConsigneeCity") as ASPxButtonEdit;
whTrans.ConsigneeCity = txt_ConsigneeCity.Text;
ASPxButtonEdit btn_Lc = grd_Do.FindEditFormTemplateControl("btn_Lc") as ASPxButtonEdit;
whTrans.LcNo = btn_Lc.Text;
if (action)
{
whTrans.DoNo = poNo;
whTrans.DoType = "BP";
whTrans.CreateBy = EzshipHelper.GetUserName();
whTrans.CreateDateTime = DateTime.Now;
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whTrans);
C2Setup.SetNextNo("", "BlanketPO", poNo, txt_Date.Date);
}
else
{
whTrans.UpdateBy = EzshipHelper.GetUserName();
whTrans.UpdateDateTime = DateTime.Now;
bool isAddLog = false;
if (whTrans.DoStatus == SafeValue.SafeString(ConnectSql.ExecuteScalar("Select DoStatus from wh_trans where DoNo='" + whTrans.DoNo + "'")))
{
}
else
{
isAddLog = true;
}
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Updated);
Manager.ORManager.PersistChanges(whTrans);
if (isAddLog)
{
EzshipLog.Log(whTrans.DoNo, "", "PO", whTrans.DoStatus);
}
}
Session["BPWhere"] = "DoNo='" + whTrans.DoNo + "'";
this.dsDo.FilterExpression = Session["BPWhere"].ToString();
if (this.grd_Do.GetRow(0) != null)
{
this.grd_Do.StartEdit(0);
}
return(whTrans.DoNo);
}
catch { }
return("");
}
protected void btn_CreatePo_Click(object sender, EventArgs e)
{
string poNo = "";
// string sql = string.Format(@"Select DISTINCT tab.Product,tab.ProductClass,tab.HandQty,tab.HsCode,tab.Description,tab.Att4,tab.Att5,tab.Att6,tab.Att7,tab.Att8,tab.Att9,Uom1,MinOrderQty,WareHouseId,NewOrderQty,0 as Qty,isnull(tab_Po.Price,0) as Price from (
//select tab_hand.Product,tab_in.Packing,WareHouseId,tab_in.HsCode,tab_in.ProductClass
//,tab_in.Description,tab_hand.Qty1-isnull(tab_out.Qty1,0) as HandQty,UomPacking as Uom1,Att4,Att5,Att6,Att7,Att8,Att9,0 as Qty
// from (select DISTINCT p.Code,mast.WareHouseId,p.Description,p.QtyPackingWhole as packing,p.HsCode,p.ProductClass,p.Att4,p.Att5,p.Att6,p.Att7,p.Att8,p.Att9,p.UomPacking from wh_dodet2 det inner join wh_do mast on mast.DoNo=det.DoNo and mast.DoType=det.DoType and mast.StatusCode='CLS'
//left join ref_product p on p.code=det.Product where det.Dotype='IN' and len(det.doNo)>0 ) as tab_in
//left join (select product ,sum(Qty1) as Qty1,sum(Qty2) as Qty2,sum(Qty3) as Qty3 from wh_dodet2 det inner join wh_do mast on mast.DoNo=det.DoNo and mast.StatusCode='CLS' where det.DoType='IN' and len(det.DoNo)>0 group by product) as tab_hand on tab_hand.Product=tab_in.Code
//left join (select Product,sum(Qty1) as Qty1,sum(Qty2) as Qty2,sum(Qty3) as Qty3 from wh_dodet2 det inner join Wh_DO mast on det.DoNo=mast.DoNo and (mast.StatusCode='CLS' or mast.StatusCode='RETURN') where det.DoType='Out' and len(det.DoNo)>0 group by Product) as tab_out on tab_out.Product=tab_hand.Product
//left join (select ProductCode,sum(Case when DATEDIFF(week,getdate(),mast.ExpectedDate)<0 then Qty5 else 0 end) as InQty_0
// from wh_doDet det inner join Wh_do mast on det.DoNo=mast.DoNo and det.DoType=mast.DoType where det.DoType='IN' and mast.StatusCode!='CNL' group by productCode
//) as tab_Incoming on tab_Incoming.ProductCode=tab_hand.product) as tab left join ref_product p on p.Code=tab.Product
//left join (select top(1) price,ProductCode from Wh_TransDet det inner join Wh_Trans mast on det.DoNo=mast.DoNo and det.DoType=mast.DoType and mast.DoType='PO' and mast.DoStatus='Closed' order by mast.DoDate,det.Price Desc) tab_Po on tab_Po.ProductCode=tab.Product
//left join (select Product,SUM(case when det.DoType='IN' then Qty1 else isnull(-Qty1,0) end) as BalQty from wh_dodet2 det inner join wh_do mast on mast.DoNo=det.DoNo and mast.StatusCode='CLS' where det.DoType='IN' and len(det.DoNo)>0 group by product) as tab_Bal on tab_Bal.Product=p.Code
//where 1=1 and HandQty>0 and p.MinOrderQty>tab_Bal.BalQty");
// string sql = string.Format(@"Select DISTINCT * from (
//select p.Code,WareHouseId,p.HsCode,p.ProductClass,tab_in.PartyId
//,p.Description,ISNULL(tab_hand.Qty1-isnull(tab_out.Qty1,0),0) as HandQty,p.UomPacking as Uom1,p.MinOrderQty,p.Att4,p.Att5,p.Att6,p.Att7,p.Att8,p.Att9,0 as Qty,0 as Price
// from ref_product p
//left join (select det.Product,mast.WareHouseId,det.Des1,det.packing,p.HsCode,p.ProductClass,Uom1,p.PartyId from wh_dodet2 det inner join wh_do mast on mast.DoNo=det.DoNo and mast.DoType=det.DoType and mast.StatusCode!='CNL'
//left join ref_product p on p.code=det.Product where det.Dotype='IN' and len(det.doNo)>0 ) as tab_in on p.Code=tab_in.Product
//left join (select product ,sum(Qty1) as Qty1,sum(Qty2) as Qty2,sum(Qty3) as Qty3 from wh_dodet2 det inner join wh_do mast on mast.DoNo=det.DoNo and mast.StatusCode='CLS' where det.DoType='IN' and len(det.DoNo)>0 group by product) as tab_hand on tab_hand.Product=tab_in.Product
//left join (select Product,sum(Qty1) as Qty1,sum(Qty2) as Qty2,sum(Qty3) as Qty3 from wh_dodet2 det inner join Wh_DO mast on det.DoNo=mast.DoNo and (mast.StatusCode='CLS' or mast.StatusCode='RETURN') where det.DoType='Out' and len(det.DoNo)>0 group by Product) as tab_out on tab_out.Product=tab_hand.Product
//left join (select ProductCode,sum(Case when DATEDIFF(week,getdate(),mast.ExpectedDate)<0 then Qty5 else 0 end) as InQty_0
// from wh_doDet det inner join Wh_do mast on det.DoNo=mast.DoNo and det.DoType=mast.DoType where det.DoType='IN' and mast.StatusCode!='CNL' group by productCode
//) as tab_Incoming on tab_Incoming.ProductCode=tab_hand.product) as tab
//left join (select Product,SUM(case when det.DoType='IN' then Qty1 else isnull(-Qty1,0) end) as BalQty from wh_dodet2 det inner join wh_do mast on mast.DoNo=det.DoNo and mast.StatusCode='CLS' where det.DoType='IN' and len(det.DoNo)>0 group by product) as tab_Bal on tab_Bal.Product=tab.Code
//where MinOrderQty>ISNULL(BalQty,0) and MinOrderQty>0");
//DataTable tab = new DataTable();
if (list.Count > 0)
{
WhTrans whTrans = new WhTrans();
poNo = C2Setup.GetNextNo("", "PurchaseOrders", DateTime.Now);
whTrans.DoDate = DateTime.Now;
whTrans.DoNo = poNo;
whTrans.DoType = "PO";
whTrans.DoStatus = "Draft";
whTrans.ExpectedDate = DateTime.Today.AddDays(14);
whTrans.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
whTrans.ExRate = SafeValue.SafeDecimal(1.000000);
whTrans.WareHouseId = System.Configuration.ConfigurationManager.AppSettings["WareHouse"];
whTrans.CreateBy = EzshipHelper.GetUserName();
whTrans.CreateDateTime = DateTime.Now;
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whTrans);
C2Setup.SetNextNo("", "PurchaseOrders", poNo, DateTime.Now);
for (int i = 0; i < list.Count; i++)
{
string sku = list[i].code;
string des = list[i].des;
string att4 = list[i].att4;
string att5 = list[i].att5;
string att6 = list[i].att6;
string att7 = list[i].att7;
string att8 = list[i].att8;
string att9 = list[i].att9;
string uom1 = list[i].uom1;
//sql += string.Format(@" and Code='{0}' ", sku);
//tab = ConnectSql.GetTab(sql);
WhTransDet whTransDet = new WhTransDet();
whTransDet.DoNo = poNo;
whTransDet.DoType = "PO";
whTransDet.ProductCode = sku;
whTransDet.Qty1 = list[i].qty;
whTransDet.Des1 = des;
whTransDet.Att1 = att4;
whTransDet.Att2 = att5;
whTransDet.Att3 = att6;
whTransDet.Att4 = att7;
whTransDet.Att5 = att8;
whTransDet.Att6 = att9;
whTransDet.Uom1 = uom1;
whTransDet.Price = list[i].price;
whTransDet.Currency = System.Configuration.ConfigurationManager.AppSettings["Currency"];
whTransDet.ExRate = SafeValue.SafeDecimal(1.000000);
whTransDet.DocAmt = SafeValue.SafeDecimal(whTransDet.Qty1 * whTransDet.Price);
Manager.ORManager.StartTracking(whTransDet, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whTransDet);
}
Response.Redirect("/WareHouse/Job/PoEdit.aspx?no=" + poNo);
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
string doNo = SafeValue.SafeString(Request.QueryString["Sn"].ToString());
string whId = SafeValue.SafeString(Request.QueryString["WhId"].ToString());
string partyId = SafeValue.SafeString(Request.QueryString["partyId"].ToString());
string s = e.Parameters;
if (s == "Save")
{
#region Create PO
string sql = string.Format(@"select Count(*) from Wh_TransDet where DoNo='{0}'", doNo);
int cnt = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (cnt > 0)
{
bool action = false;
for (int i = 0; i < list.Count; i++)
{
int totalQty = list[i].pickQty;
int qty = list[i].qty;
if (totalQty < qty)
{
action = false;
e.Result = "Fail! Pls enter again";
return;
}
else
{
action = true;
}
}
if (action)
{
string pId = "";
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(WhTrans), "Id='" + pId + "'");
WhTrans whTrans = C2.Manager.ORManager.GetObject(query) as WhTrans;
whTrans = new WhTrans();
string poNo = C2Setup.GetNextNo("", "PurchaseOrders", DateTime.Now);
whTrans.DoNo = poNo;
whTrans.DoType = "PO";
whTrans.PartyId = partyId;
whTrans.PartyName = SafeValue.SafeString(partyId);
whTrans.CreateBy = EzshipHelper.GetUserName();
whTrans.CreateDateTime = DateTime.Now;
whTrans.DoDate = DateTime.Now;
whTrans.ExpectedDate = DateTime.Today.AddDays(14);
whTrans.Currency = "SGD";
whTrans.DoStatus = "Draft";
whTrans.ExRate = SafeValue.SafeDecimal(1.000000);
whTrans.WareHouseId = whId;
Manager.ORManager.StartTracking(whTrans, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(whTrans);
C2Setup.SetNextNo("", "PurchaseOrders", poNo, DateTime.Now);
for (int i = 0; i < list.Count; i++)
{
string sku = list[i].sku;
//string lotNo = list[i].lotNo;
int qty = list[i].qty;
decimal price = list[i].price;
// string location = list[i].loc;
sql = @"Insert Into Wh_TransDet(DoNo,ProductCode,DoType,Qty1,Price,LotNo,Uom1,Uom2,Uom3,Uom4,[QtyPackWhole],[QtyWholeLoose],[QtyLooseBase],[CreateBy],[CreateDateTime],[UpdateBy],[UpdateDateTime],Att1,Att2,Att3,Att4,Att5,Att6,Des1,Packing,LocationCode)";
sql += string.Format(@"select '{0}'as DoNo, '{1}' as Sku,'PO','{2}' as Qty1,'{3}','{5}',p.UomPacking,p.UomWhole,p.UomLoose,p.UomBase,p.QtyPackingWhole,p.QtyWholeLoose,p.QtyLooseBase,'{4}' as CreateBy,getdate() as CreateDateTime,
'{4}' as UpdateBy,getdate() as UpdateDateTime
,P.att4,P.att5,P.att6,P.att7,P.att8,P.att9,p.Description,p.Att1,'{6}'
from (select '{1}' as Sku) as tab
left join ref_product p on tab.Sku=p.Code", poNo, sku, qty, price, EzshipHelper.GetUserName(), "", "");
ConnectSql.ExecuteSql(sql);
}
e.Result = "Success! " + "Purchase Order No is" + whTrans.DoNo;
}
}
else
{
e.Result = "Fail";
}
#endregion
}
}