private void CreateInv(string invN, int id, string docId) { string sql = string.Format(@"select * from job_cost where Id={0}", id); DataTable dt = ConnectSql.GetRemoteTab(sql); string sql_part1 = string.Format(@"insert into XAArInvoiceDet (DocId,DocNo,DocType,DocLineNo,AcCode,AcSource,ChgCode,ChgDes1, GstType,Qty,Price,Unit,Currency,ExRate,Gst,GstAmt,DocAmt,LocAmt,LineLocAmt,MastRefNo,JobRefNo,MastType,JobCostId) values"); for (int i = 0; i < dt.Rows.Count; i++) { sql = ""; string chgCodeId = SafeValue.SafeString(dt.Rows[i]["ChgCode"]); string chgCodeDes = SafeValue.SafeString(dt.Rows[i]["ChgCodeDes"]); decimal price = SafeValue.SafeDecimal(dt.Rows[i]["Price"]); string cntNo = SafeValue.SafeString(dt.Rows[i]["ContNo"]); string cntType = SafeValue.SafeString(dt.Rows[i]["ContType"]); string jobNo = SafeValue.SafeString(dt.Rows[i]["JobNo"]); string billType = SafeValue.SafeString(cbb_BillType.Text, ""); string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes,GstP,GstTypeId,ChgTypeId from XXChgCode where chgCodeId='{0}'", chgCodeId); DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode); decimal gst = 0; string gstType = ""; string chgTypeId = ""; if (dt_chgCode.Rows.Count > 0) { gst = SafeValue.SafeDecimal(dt_chgCode.Rows[0]["GstP"]); gstType = SafeValue.SafeString(dt_chgCode.Rows[0]["GstTypeId"]); chgTypeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgTypeId"]); chgCodeDes = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgCodeDes"]); } decimal amt = SafeValue.ChinaRound(1 * SafeValue.SafeDecimal(price, 0), 2); decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(gst, 0)), 2); decimal docAmt = amt + gstAmt; decimal locAmt = SafeValue.ChinaRound(docAmt * SafeValue.SafeDecimal(1, 1), 2); if (chgCodeDes.ToUpper().Equals("TRUCKING")) { chgCodeDes += " " + billType.ToUpper(); } string sql_part2 = string.Format(@"('{0}','{1}','IV','{2}','','CR','{3}','{4}','{14}',1,{5},'{9}','SGD',1,{10},{11},{12},{13},0,'{6}','{7}','{8}',{15}) select @@IDENTITY ", docId, invN, i + 1, chgCodeId, chgCodeDes, price, jobNo, cntNo, chgTypeId, cntType, gst, gstAmt, docAmt, locAmt, gstType, id); sql += sql.Length > 0 ? "," + sql_part2 : sql_part2; } if (sql.Length > 0) { sql = sql_part1 + sql; int re = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0); UpdateMaster(SafeValue.SafeInt(docId, 0)); UpdateLineId(id, re); } }
public static void CreateWhInv(string invN, int id, string docId) { string sql = string.Format(@"select c.*,det3.ContainerNo,det3.ContainerType from Wh_Costing c left join wh_dodet3 det3 on c.RefNo=det3.DoNo where c.Id={0}", id); DataTable dt = ConnectSql.GetRemoteTab(sql); string sql_part1 = string.Format(@"insert into XAArInvoiceDet (DocId,DocNo,DocType,DocLineNo,AcCode,AcSource,ChgCode,ChgDes1, ChgDes2, ChgDes3, GstType,Qty,Price,Unit,Currency,ExRate,Gst,GstAmt,DocAmt,LocAmt,LineLocAmt,MastRefNo,JobRefNo,MastType) values"); for (int i = 0; i < dt.Rows.Count; i++) { sql = ""; string chgCodeId = SafeValue.SafeString(dt.Rows[i]["ChgCode"]); string cheCodeDes = SafeValue.SafeString(dt.Rows[i]["ChgCodeDes"]); decimal price = SafeValue.SafeDecimal(dt.Rows[i]["CostLocAmt"]); string cntNo = SafeValue.SafeString(dt.Rows[i]["ContainerNo"]); string cntType = SafeValue.SafeString(dt.Rows[i]["ContainerType"]); string jobNo = SafeValue.SafeString(dt.Rows[i]["RefNo"]); string gstType = SafeValue.SafeString(dt.Rows[i]["CostGstType"]); string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes,GstP,GstTypeId,ChgTypeId from XXChgCode where ChgcodeDes='{0}'", chgCodeId); DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode); decimal gst = 0; string chgTypeId = ""; if (dt_chgCode.Rows.Count > 0) { gst = SafeValue.SafeDecimal(dt_chgCode.Rows[0]["GstP"]); gstType = SafeValue.SafeString(dt_chgCode.Rows[0]["GstTypeId"]); chgTypeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgTypeId"]); } decimal amt = SafeValue.ChinaRound(1 * SafeValue.SafeDecimal(price, 0), 2); decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(gst, 0)), 2); decimal docAmt = amt; decimal locAmt = SafeValue.ChinaRound(docAmt * SafeValue.SafeDecimal(1, 1), 2); string sql_part2 = string.Format(@"('{0}','{1}','IV','{2}','','CR','{3}','{4}','','','{14}',1,{5},'{9}','SGD',1,{10},{11},{12},{13},0,'{6}','{7}','{8}')", docId, invN, i + 1, chgCodeId, cheCodeDes, price, jobNo, cntNo, chgTypeId, cntType, gst, gstAmt, docAmt, locAmt, gstType); sql += sql.Length > 0 ? "," + sql_part2 : sql_part2; } if (sql.Length > 0) { sql = sql_part1 + sql; int re = ConnectSql.ExecuteSql(sql); C2.XAArInvoice.update_invoice_mast(SafeValue.SafeInt(docId, 0)); //UpdateMaster(SafeValue.SafeInt(docId, 0)); } }