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));
}
}