protected void grid_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e)
{
e.NewValues["RefNo"] = SafeValue.SafeString(e.NewValues["RefNo"]);
e.NewValues["OriginAdd"] = SafeValue.SafeString(e.NewValues["OriginAdd"]);
e.NewValues["DestinationAdd"] = SafeValue.SafeString(e.NewValues["DestinationAdd"]);
e.NewValues["CustomerName"] = SafeValue.SafeString(e.NewValues["CustomerName"]);
e.NewValues["Contact"] = SafeValue.SafeString(e.NewValues["Contact"]);
e.NewValues["TruckNo"] = SafeValue.SafeString(e.NewValues["TruckNo"]);
e.NewValues["Value1"] = SafeValue.SafeString(e.NewValues["Value1"]);
e.NewValues["Value2"] = SafeValue.SafeString(e.NewValues["Value2"]);
e.NewValues["Value3"] = SafeValue.SafeString(e.NewValues["Value3"]);
e.NewValues["WorkStatus"] = SafeValue.SafeString(e.NewValues["WorkStatus"]);
e.NewValues["JobDate"] = SafeValue.SafeDate(e.NewValues["JobDate"], DateTime.Now);
e.NewValues["MoveDate"] = SafeValue.SafeDate(e.NewValues["JobDate"], DateTime.Now);
e.NewValues["PackRmk"] = SafeValue.SafeString(e.NewValues["PackRmk"]);
e.NewValues["MoveRmk"] = SafeValue.SafeString(e.NewValues["MoveRmk"]);
e.NewValues["TruckNo"] = SafeValue.SafeString(e.NewValues["TruckNo"]);
e.NewValues["VolumneRmk"] = SafeValue.SafeString(e.NewValues["VolumneRmk"]);
e.NewValues["Note1"] = SafeValue.SafeString(e.NewValues["Note1"]);
e.NewValues["Note2"] = SafeValue.SafeString(e.NewValues["Note2"]);
DateTime jobDate = SafeValue.SafeDate(e.NewValues["JobDate"], DateTime.Now);
ASPxTimeEdit date_Time = grid.FindEditRowCellTemplateControl(null, "date_Time") as ASPxTimeEdit;
string jobTime = SafeValue.SafeDateStr(e.NewValues["JobDate"]);
DateTime moveDate = DateTime.Parse(jobDate.ToString("yyyy-MM-dd") + " " + date_Time.Text);
e.NewValues["MoveDate"] = SafeValue.SafeDate(moveDate, DateTime.Now);
//e.NewValues["MoveDate"] = SafeValue.SafeDate(date_Time.Value, DateTime.Now);
}
static public string GetDoDateByDoNo(object doNo)
{
if (SafeValue.SafeString(doNo, "").Length > 0)
{
string sql = string.Format("select DoDate from Wh_Do where DoNo='{0}'", doNo);
return(SafeValue.SafeDateStr(C2.Manager.ORManager.ExecuteScalar(sql)));
}
return("");
}
protected void grid_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e)
{
e.NewValues["JobType"] = "OUT3";
e.NewValues["Value1"] = "Pending";
e.NewValues["OriginAdd"] = SafeValue.SafeString(e.NewValues["OriginAdd"]);
e.NewValues["DestinationAdd"] = SafeValue.SafeString(e.NewValues["DestinationAdd"]);
e.NewValues["CustomerName"] = SafeValue.SafeString(e.NewValues["CustomerName"]);
e.NewValues["Contact"] = SafeValue.SafeString(e.NewValues["Contact"]);
e.NewValues["TruckNo"] = SafeValue.SafeString(e.NewValues["TruckNo"]);
e.NewValues["Value1"] = SafeValue.SafeString(e.NewValues["Value1"]);
e.NewValues["Value2"] = SafeValue.SafeString(e.NewValues["Value2"]);
e.NewValues["Value3"] = SafeValue.SafeString(e.NewValues["Value3"]);
//e.NewValues["WorkStatus"] = SafeValue.SafeString(e.NewValues["WorkStatus"]);
e.NewValues["JobDate"] = SafeValue.SafeDate(e.NewValues["JobDate"], DateTime.Now);
e.NewValues["MoveDate"] = SafeValue.SafeDate(e.NewValues["JobDate"], DateTime.Now);
e.NewValues["PackRmk"] = SafeValue.SafeString(e.NewValues["PackRmk"]);
e.NewValues["MoveRmk"] = SafeValue.SafeString(e.NewValues["MoveRmk"]);
e.NewValues["TruckNo"] = SafeValue.SafeString(e.NewValues["TruckNo"]);
e.NewValues["VolumneRmk"] = SafeValue.SafeString(e.NewValues["VolumneRmk"]);
e.NewValues["Note1"] = SafeValue.SafeString(e.NewValues["Note1"]);
e.NewValues["Note2"] = SafeValue.SafeString(e.NewValues["Note2"]);
e.NewValues["Value4"] = SafeValue.SafeString(e.NewValues["Value4"]);
e.NewValues["Value5"] = SafeValue.SafeString(e.NewValues["Value5"]);
e.NewValues["DateTime1"] = SafeValue.SafeDate(e.NewValues["DateTime1"], DateTime.Now);
e.NewValues["DateTime2"] = SafeValue.SafeDate(e.NewValues["DateTime2"], DateTime.Now);
e.NewValues["DateTime3"] = SafeValue.SafeDate(e.NewValues["DateTime3"], DateTime.Now);
e.NewValues["DateTime4"] = SafeValue.SafeDate(e.NewValues["DateTime4"], DateTime.Now);
e.NewValues["DateTime5"] = SafeValue.SafeDate(e.NewValues["DateTime5"], DateTime.Now);
DateTime jobDate = SafeValue.SafeDate(e.NewValues["JobDate"], DateTime.Now);
ASPxTimeEdit date_Time = grid.FindEditRowCellTemplateControl(null, "date_Time") as ASPxTimeEdit;
string jobTime = SafeValue.SafeDateStr(e.NewValues["JobDate"]);
DateTime moveDate = DateTime.Parse(jobDate.ToString("yyyy-MM-dd") + " " + date_Time.Text);
e.NewValues["MoveDate"] = SafeValue.SafeDate(moveDate, DateTime.Now);
string refNo = SafeValue.SafeString(e.NewValues["RefNo"]);
e.NewValues["RefNo"] = refNo;
C2Setup.SetNextNo("", "Materials-OUT3", refNo, DateTime.Now);
e.NewValues["WorkStatus"] = "Cancel";
}
public static DataSet DsBankReceipt(DateTime d1, DateTime d2, string acCode, string userId)
{
DataSet set = new DataSet();
DataTable mast = new DataTable("Mast");
mast.Columns.Add("AcCode");
mast.Columns.Add("AcDesc");
mast.Columns.Add("DatePeriod");
mast.Columns.Add("UserId");
DataTable det = new DataTable("Detail");
det.Columns.Add("AcCode");
det.Columns.Add("DocDate");
det.Columns.Add("DocType");
det.Columns.Add("DocNo");
det.Columns.Add("ChqNo");
det.Columns.Add("ChqDate");
det.Columns.Add("DocAmt");
det.Columns.Add("LocAmt");
det.Columns.Add("Status");
det.Columns.Add("ClearDate");
string sql = string.Format(@"SELECT DocType, DocNo, DocDate, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, BankRec, BankDate
FROM XAArReceipt where DocType='RE' and DocDate>='{0}' and DocDate<'{1}' and AcCode='{2}'", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"), acCode);
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectString"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
string docNo = reader["DocNo"].ToString();
string docType = reader["DocType"].ToString();
string docDate = SafeValue.SafeDateStr(reader["DocDate"]);
string chqNo = reader["ChqNo"].ToString();
string chqDate = SafeValue.SafeDateStr(reader["ChqDate"]);
decimal docAmt = SafeValue.SafeDecimal(reader["DocAmt"], 0);
decimal locAmt = SafeValue.SafeDecimal(reader["LocAmt"], 0);
string status = SafeValue.SafeString(reader["BankRec"], "N");
if (chqNo != "CASH1")//chqNo.Length > 0 &&
{
string clearDate = SafeValue.SafeDateStr(reader["BankDate"]);
if (status == "Y")
{
status = "CLEARED";
}
else
{
status = "UNCLEARED";
clearDate = "";
}
DataRow row = det.NewRow();
row["AcCode"] = acCode;
row["DocDate"] = docDate;
row["DocType"] = docType;
row["DocNo"] = docNo;
row["ChqNo"] = chqNo;
row["ChqDate"] = chqDate;
row["DocAmt"] = docAmt.ToString("#,##0.00");
row["LocAmt"] = docAmt.ToString("#,##0.00");
row["Status"] = status;
row["ClearDate"] = clearDate;
det.Rows.Add(row);
}
}
reader.Close();
reader.Dispose();
sql = string.Format(@"SELECT DocType, DocNo, DocDate, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, BankRec, BankDate
FROM XAApPayment where DocType='SR' and DocDate>='{0}' and DocDate<'{1}' and AcCode='{2}'", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"), acCode);
//ps
SqlConnection con1 = new SqlConnection(conStr);
con1.Open();
SqlCommand cmd1 = new SqlCommand(sql, con1);
SqlDataReader reader1 = cmd1.ExecuteReader();
// Call Read before accessing data.
while (reader1.Read())
{
string docNo = reader1["DocNo"].ToString();
string docType = reader1["DocType"].ToString();
string docDate = SafeValue.SafeDateStr(reader1["DocDate"]);
string chqNo = reader1["ChqNo"].ToString();
string chqDate = SafeValue.SafeDateStr(reader1["ChqDate"]);
decimal docAmt = SafeValue.SafeDecimal(reader1["DocAmt"], 0);
decimal locAmt = SafeValue.SafeDecimal(reader1["LocAmt"], 0);
string status = SafeValue.SafeString(reader1["BankRec"], "N");
if (chqNo != "CASH")//chqNo.Length > 0 &&
{
string clearDate = SafeValue.SafeDateStr(reader1["BankDate"]);
if (status == "Y")
{
status = "CLEARED";
}
else
{
status = "UNCLEARED";
clearDate = "";
}
DataRow row = det.NewRow();
row["AcCode"] = acCode;
row["DocDate"] = docDate;
row["DocType"] = docType;
row["DocNo"] = docNo;
row["ChqNo"] = chqNo;
row["ChqDate"] = chqDate;
row["DocAmt"] = docAmt.ToString("#,##0.00");
row["LocAmt"] = docAmt.ToString("#,##0.00");
row["Status"] = status;
row["ClearDate"] = clearDate;
det.Rows.Add(row);
}
}
DataRow rowMast = mast.NewRow();
rowMast["AcCode"] = acCode;
rowMast["AcDesc"] = GetObj("SELECT AcDesc FROM XXChartAcc WHERE Code ='" + acCode + "'");
rowMast["DatePeriod"] = string.Format("From {0} To {1}", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"));
rowMast["UserId"] = userId;
mast.Rows.Add(rowMast);
set.Tables.Add(mast);
set.Tables.Add(det);
DataRelation r = new DataRelation("", mast.Columns["AcCode"], det.Columns["AcCode"]);
set.Relations.Add(r);
return(set);
}
public static DataSet DsBankRecon(DateTime d1, string balAmt, string acCode, string userId)
{
DataSet set = new DataSet();
DataTable mast = new DataTable("Mast");
mast.Columns.Add("AcCode");
mast.Columns.Add("AcDesc");
mast.Columns.Add("DatePeriod");
mast.Columns.Add("UserId");
mast.Columns.Add("BalAmt");
mast.Columns.Add("TotAmt");
DataTable det = new DataTable("Detail");
det.Columns.Add("AcCode");
det.Columns.Add("ChqNo");
det.Columns.Add("ChqDate");
det.Columns.Add("ChqType");
det.Columns.Add("ChqInfo");
det.Columns.Add("DocAmt");
decimal totAmt = SafeValue.SafeDecimal(balAmt, 0);
//pc
string sql = string.Format(@"SELECT DocType, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate
FROM XAArReceipt where DocDate<'{0}' and AcCode='{1}' and (BankRec='' OR BankRec='N')", d1.AddDays(1).ToString("yyyy-MM-dd"), acCode);
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectString"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
string chqNo = reader["ChqNo"].ToString();
string chqDate = SafeValue.SafeDateStr(reader["ChqDate"]);
decimal docAmt = SafeValue.SafeDecimal(reader["DocAmt"], 0);
string chqType = reader["DocType"].ToString();
//decimal locAmt = SafeValue.SafeDecimal(reader["LocAmt"], 0);
//if (chqNo != "CASH" && docAmt > 0)//chqNo.Length > 0 &&
//{
DataRow row = det.NewRow();
row["AcCode"] = acCode;
row["ChqNo"] = chqNo;
row["ChqDate"] = chqDate;
if (chqType == "RE")
{
row["ChqType"] = "RE";
row["ChqInfo"] = "Add : Uncredited Deposit";
row["DocAmt"] = docAmt.ToString("#,##0.00");
totAmt += docAmt;
}
else
{
row["ChqType"] = "PC";;
row["ChqInfo"] = "Less: UnPresented Cheque";
row["DocAmt"] = (-docAmt).ToString("#,##0.00");
totAmt -= docAmt;
}
det.Rows.Add(row);
//}
}
reader.Close();
reader.Dispose();
sql = string.Format(@"SELECT DocType, DocAmt, LocAmt, AcCode, AcSource, ChqNo, ChqDate, BankRec, BankDate
FROM XAApPayment where DocType='PS' and DocDate<='{0}' and AcCode='{1}' and (BankRec='' OR BankRec='N')", d1.AddDays(1).ToString("yyyy-MM-dd"), acCode);
//ps
//throw new Exception(sql);
SqlConnection con1 = new SqlConnection(conStr);
con1.Open();
SqlCommand cmd1 = new SqlCommand(sql, con1);
SqlDataReader reader1 = cmd1.ExecuteReader();
// Call Read before accessing data.
while (reader1.Read())
{
string chqNo = reader1["ChqNo"].ToString();
string chqDate = SafeValue.SafeDateStr(reader1["ChqDate"]);
decimal docAmt = SafeValue.SafeDecimal(reader1["DocAmt"], 0);
//decimal locAmt = SafeValue.SafeDecimal(reader1["LocAmt"], 0);
string chqType = reader1["DocType"].ToString();
//if (chqNo != "CASH" && docAmt > 0)//chqNo.Length > 0 &&
//{
DataRow row = det.NewRow();
row["AcCode"] = acCode;
row["ChqNo"] = chqNo;
row["ChqDate"] = chqDate;
// row["LocAmt"] = docAmt.ToString("#,##0.00");
if (chqType == "SR")
{
row["ChqType"] = "RE";
row["ChqInfo"] = "Add : Uncredited Deposit";
row["DocAmt"] = docAmt.ToString("#,##0.00");
totAmt += docAmt;
}
else
{
row["ChqType"] = "PC";;
row["ChqInfo"] = "Less: UnPresented Cheque";
row["DocAmt"] = (-docAmt).ToString("#,##0.00");
totAmt -= docAmt;
};
det.Rows.Add(row);
//}
}
DataRow rowMast = mast.NewRow();
rowMast["AcCode"] = acCode;
rowMast["AcDesc"] = GetObj("SELECT AcDesc FROM XXChartAcc WHERE Code ='" + acCode + "'");
rowMast["DatePeriod"] = string.Format("Up to {0}", d1.ToString("dd/MM/yyyy"));
rowMast["UserId"] = userId;
rowMast["BalAmt"] = SafeValue.SafeDecimal(balAmt, 0).ToString("0.00");
rowMast["TotAmt"] = totAmt.ToString("0.00");
mast.Rows.Add(rowMast);
set.Tables.Add(mast);
set.Tables.Add(det);
DataRelation r = new DataRelation("", mast.Columns["AcCode"], det.Columns["AcCode"]);
set.Relations.Add(r);
return(set);
}
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 DsJournalEntry(string docId)
{
string sqlMast = string.Format(@"SELECT SequenceId, DocNo, DocType, DocDate, CurrencyId, ExRate, CrAmt, DbAmt, CurrencyCrAmt, CurrencyDbAmt, Remark, UserId, EntryDate
FROM XAGlEntry WHERE (SequenceId = '{0}')", docId);
string sqlDet = string.Format(@"SELECT GlLineNo, AcCode, AcSource, CurrencyId, ExRate, CrAmt, DbAmt, CurrencyCrAmt, CurrencyDbAmt, Remark
FROM XAGlEntryDet WHERE (GlNo = '{0}')", docId);
DataTable mast = new DataTable("mast");
mast.Columns.Add("Oid");
mast.Columns.Add("DocNo");
mast.Columns.Add("DocType");
mast.Columns.Add("DocDate");
mast.Columns.Add("Currency");
mast.Columns.Add("ExRate");
mast.Columns.Add("DocAmt");
mast.Columns.Add("LocAmt");
mast.Columns.Add("Remark");
mast.Columns.Add("UserId");
mast.Columns.Add("CustName");
mast.Columns.Add("CrAmt");
mast.Columns.Add("DbAmt");
DataTable det = new DataTable("Detail");
det.Columns.Add("Oid");
det.Columns.Add("AcCode");
det.Columns.Add("AcDes");
det.Columns.Add("AcSource");
det.Columns.Add("Rmk");
det.Columns.Add("Currency");
det.Columns.Add("ExRate");
det.Columns.Add("CrAmt");
det.Columns.Add("DbAmt");
try
{
DataTable tab_mast = Helper.Sql.List(sqlMast);
DataTable tab_det = Helper.Sql.List(sqlDet);
if (tab_mast.Rows.Count > 0)
{
decimal crAmt = 0;
decimal dbAmt = 0;
for (int i = 0; i < tab_det.Rows.Count; i++)
{
string acSource = tab_det.Rows[i]["AcSource"].ToString();
DataRow rowDet = det.NewRow();
rowDet["Oid"] = docId;
rowDet["AcCode"] = tab_det.Rows[i]["AcCode"];
rowDet["AcDes"] = ConnectSql.ExecuteScalar(string.Format("SELECT AcDesc FROM XXChartAcc where Code='{0}'", tab_det.Rows[i]["AcCode"]));
rowDet["AcSource"] = tab_det.Rows[i]["AcSource"];
rowDet["Rmk"] = tab_det.Rows[i]["Remark"];
rowDet["Currency"] = tab_det.Rows[i]["CurrencyId"];
rowDet["ExRate"] = SafeValue.SafeDecimal(tab_det.Rows[i]["ExRate"], 1).ToString("0.000");
if (acSource == "DB")
{
rowDet["DbAmt"] = SafeValue.SafeDecimal(tab_det.Rows[i]["CurrencyDbAmt"], 0).ToString("#,##0.00");
dbAmt += SafeValue.SafeDecimal(tab_det.Rows[i]["CurrencyDbAmt"], 0);
}
else
{
rowDet["CrAmt"] = SafeValue.SafeDecimal(tab_det.Rows[i]["CurrencyCrAmt"], 0).ToString("#,##0.00");
crAmt += SafeValue.SafeDecimal(tab_det.Rows[i]["CurrencyCrAmt"], 0);
}
det.Rows.Add(rowDet);
}
DataRow row = mast.NewRow();
row["Oid"] = docId;
row["DocNo"] = tab_mast.Rows[0]["DocNo"].ToString();
row["DocType"] = tab_mast.Rows[0]["DocType"];
row["Remark"] = tab_mast.Rows[0]["Remark"];
row["DocDate"] = SafeValue.SafeDateStr(tab_mast.Rows[0]["DocDate"]);
decimal exRate = SafeValue.SafeDecimal(tab_mast.Rows[0]["ExRate"], 1);
string currency = tab_mast.Rows[0]["CurrencyId"].ToString();
row["Currency"] = currency;
row["ExRate"] = exRate.ToString("#,##0.000");
row["DocAmt"] = SafeValue.SafeDecimal(tab_mast.Rows[0]["CrAmt"], 1).ToString("#,##0.00");
row["LocAmt"] = SafeValue.SafeDecimal(tab_mast.Rows[0]["CurrencyCrAmt"], 1).ToString("#,##0.00");
row["UserId"] = tab_mast.Rows[0]["UserId"];
row["CustName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["CrAmt"] = crAmt.ToString("#,##0.00");
row["DbAmt"] = dbAmt.ToString("#,##0.00");
mast.Rows.Add(row);
}
}
catch
{
}
DataSet ds = new DataSet();
ds.Tables.Add(mast);
ds.Tables.Add(det);
DataRelation r = new DataRelation("", mast.Columns["Oid"], det.Columns["Oid"]);
ds.Relations.Add(r);
return(ds);
}
public static DataSet DsApPayment(string docId)
{
string sqlMast = string.Format(@"SELECT SequenceId, AcCode, DocType, DocNo, DocDate, ChqNo, ChqDate, PartyTo, OtherPartyName, CurrencyId, ExRate, DocAmt, LocAmt, UserId, Remark
FROM XAApPayment WHERE (SequenceId = '{0}')", docId);
string sqlDet = string.Format(@"SELECT AcCode,AcSource, Remark1, Remark2, Remark3, Currency, ExRate, DocAmt, LocAmt
FROM XAApPaymentDet WHERE (PayId = '{0}')", docId);
DataTable mast = new DataTable("mast");
mast.Columns.Add("Oid");
mast.Columns.Add("DocNo");
mast.Columns.Add("DocType");
mast.Columns.Add("ChqNo");
mast.Columns.Add("AcCode");
mast.Columns.Add("DocDate");
mast.Columns.Add("PartyTo");
mast.Columns.Add("Currency");
mast.Columns.Add("ExRate");
// mast.Columns.Add("BankName");
mast.Columns.Add("DocAmt");
mast.Columns.Add("LocAmt");
mast.Columns.Add("UserId");
mast.Columns.Add("Remark");
mast.Columns.Add("CompanyName");
DataTable det = new DataTable("Detail");
det.Columns.Add("Oid");
det.Columns.Add("AcCode");
det.Columns.Add("AcSource");
det.Columns.Add("Rmk");
det.Columns.Add("Currency");
det.Columns.Add("ExRate");
det.Columns.Add("DocAmt");
det.Columns.Add("LocAmt");
try
{
DataTable tab_mast = Helper.Sql.List(sqlMast);
DataTable tab_det = Helper.Sql.List(sqlDet);
if (tab_mast.Rows.Count > 0)
{
string docType = tab_mast.Rows[0]["DocType"].ToString();
string currency = tab_mast.Rows[0]["CurrencyId"].ToString().ToUpper();
decimal exRate = SafeValue.SafeDecimal(tab_mast.Rows[0]["ExRate"], 1);
decimal totLocAmt = 0;
decimal totDocAmt = 0;
if (tab_det.Rows.Count == 0)
{
DataRow rowDet = det.NewRow();
rowDet["Oid"] = docId;
rowDet["AcCode"] = "";
rowDet["Rmk"] = "";
rowDet["Currency"] = "";
rowDet["ExRate"] = "";
rowDet["DocAmt"] = "0.00";
rowDet["LocAmt"] = "0.00";
det.Rows.Add(rowDet);
}
else
{
for (int i = 0; i < tab_det.Rows.Count; i++)
{
string acSource = tab_det.Rows[i]["AcSource"].ToString();
DataRow rowDet = det.NewRow();
rowDet["Oid"] = docId;
rowDet["AcCode"] = tab_det.Rows[i]["AcCode"];
rowDet["AcSource"] = tab_det.Rows[i]["AcSource"];
rowDet["Rmk"] = tab_det.Rows[i]["Remark1"];
string lineCurrency = tab_det.Rows[i]["Currency"].ToString().ToUpper();
rowDet["Currency"] = lineCurrency;
rowDet["ExRate"] = tab_det.Rows[i]["ExRate"];
rowDet["DocAmt"] = SafeValue.SafeDecimal(tab_det.Rows[i]["DocAmt"], 0).ToString("#,##0.00");
rowDet["LocAmt"] = SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0).ToString("#,##0.00");
if (docType == "PS")
{
if (acSource == "DB")
{
totLocAmt += SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0);
if (currency == lineCurrency)
{
totDocAmt += SafeValue.SafeDecimal(tab_det.Rows[i]["DocAmt"], 0);
}
}
else
{
totLocAmt -= SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0);
if (currency == lineCurrency)
{
totDocAmt -= SafeValue.SafeDecimal(tab_det.Rows[i]["DocAmt"], 0);
}
}
}
else
{
if (acSource == "CR")
{
totLocAmt += SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0);
if (currency == lineCurrency)
{
totDocAmt += SafeValue.SafeDecimal(tab_det.Rows[i]["DocAmt"], 0);
}
}
else
{
totLocAmt -= SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0);
if (currency == lineCurrency)
{
totDocAmt -= SafeValue.SafeDecimal(tab_det.Rows[i]["DocAmt"], 0);
}
}
}
det.Rows.Add(rowDet);
}
}
DataRow row = mast.NewRow();
row["Oid"] = docId;
row["DocNo"] = tab_mast.Rows[0]["DocNo"].ToString();
row["DocType"] = docType;
row["ChqNo"] = tab_mast.Rows[0]["ChqNo"];
row["AcCode"] = tab_mast.Rows[0]["AcCode"];
row["Remark"] = tab_mast.Rows[0]["Remark"];
row["DocDate"] = SafeValue.SafeDateStr(tab_mast.Rows[0]["DocDate"]);
string partyTo = tab_mast.Rows[0]["PartyTo"].ToString();
if (partyTo.Length > 3)
{
row["PartyTo"] = EzshipHelper.GetPartyName(partyTo);
}
else
{
row["PartyTo"] = tab_mast.Rows[0]["OtherPartyName"];
}
row["Currency"] = currency;
row["ExRate"] = exRate.ToString("#,##0.00");
//row["BankName"] = tab_mast.Rows[0]["BankName"];
row["DocAmt"] = totDocAmt.ToString("#,##0.00");
row["LocAmt"] = totLocAmt.ToString("#,##0.00");
row["UserId"] = tab_mast.Rows[0]["UserId"];
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
mast.Rows.Add(row);
}
}
catch
{
}
DataSet ds = new DataSet();
ds.Tables.Add(mast);
ds.Tables.Add(det);
DataRelation r = new DataRelation("", mast.Columns["Oid"], det.Columns["Oid"]);
ds.Relations.Add(r);
return(ds);
}
public static DataSet DsArReceipt(string docId)
{
string sqlMast = string.Format(@"SELECT SequenceId,DocType, DocNo, DocDate, DocCurrency, DocExRate, PartyTo, DocAmt, LocAmt, AcCode, ChqNo, ExportInd, BankName, Remark,
UserId FROM XAArReceipt WHERE (SequenceId = '{0}')", docId);
string sqlDet = string.Format(@"SELECT AcCode,AcSource, DocId,DocType,DocAmt, LocAmt, Currency, ExRate, Remark1
FROM XAArReceiptDet WHERE (RepId = '{0}')", docId);
DataTable mast = new DataTable("mast");
mast.Columns.Add("Oid");
mast.Columns.Add("DocNo");
mast.Columns.Add("DocType");
mast.Columns.Add("ChqNo");
mast.Columns.Add("AcCode");
mast.Columns.Add("DocDate");
mast.Columns.Add("PartyTo");
mast.Columns.Add("Currency");
mast.Columns.Add("ExRate");
mast.Columns.Add("BankName");
mast.Columns.Add("DocAmt");
mast.Columns.Add("LocAmt");
mast.Columns.Add("TotDocAmt");
mast.Columns.Add("TotLocAmt");
mast.Columns.Add("UserId");
mast.Columns.Add("Remark");
DataTable det = new DataTable("Detail");
det.Columns.Add("Oid");
det.Columns.Add("AcCode");
det.Columns.Add("AcSource");
det.Columns.Add("Rmk");
det.Columns.Add("Due");
det.Columns.Add("Applied");
det.Columns.Add("ExRate");
det.Columns.Add("Local");
try
{
DataTable tab_mast = Helper.Sql.List(sqlMast);
DataTable tab_det = Helper.Sql.List(sqlDet);
if (tab_mast.Rows.Count > 0)
{
decimal totDocAmt = 0;
decimal totLocAmt = 0;
string docType = tab_mast.Rows[0]["DocType"].ToString();
decimal exRate = SafeValue.SafeDecimal(tab_mast.Rows[0]["DocExRate"], 1);
string currency = tab_mast.Rows[0]["DocCurrency"].ToString().ToUpper();
for (int i = 0; i < tab_det.Rows.Count; i++)
{
string acSource = tab_det.Rows[i]["AcSource"].ToString();
DataRow rowDet = det.NewRow();
rowDet["Oid"] = docId;
rowDet["AcCode"] = tab_det.Rows[i]["AcCode"];
rowDet["AcSource"] = acSource;
rowDet["Rmk"] = tab_det.Rows[i]["Remark1"];
decimal lineDocAmt = SafeValue.SafeDecimal(tab_det.Rows[i]["DocAmt"], 0);
decimal lineLocAmt = SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0);
string lineCurrency = SafeValue.SafeString(tab_det.Rows[i]["Currency"], "").ToUpper();
rowDet["Due"] = lineDocAmt.ToString("#,##0.00");
rowDet["Applied"] = lineDocAmt.ToString("#,##0.00");
rowDet["ExRate"] = Helper.Safe.AccountEx(tab_det.Rows[i]["ExRate"]);
rowDet["Local"] = lineLocAmt.ToString("#,##0.00");
if (docType == "RE")
{
if (acSource == "CR")
{
totLocAmt += lineLocAmt;
if (currency == lineCurrency)
{
totDocAmt += lineDocAmt;
}
}
else
{
totLocAmt -= lineLocAmt;
if (currency == lineCurrency)
{
totDocAmt -= lineDocAmt;
}
}
}
else
{
if (acSource == "DB")
{
totLocAmt += lineLocAmt;
if (currency == lineCurrency)
{
totDocAmt += lineDocAmt;
}
}
else
{
totLocAmt -= lineLocAmt;
if (currency == lineCurrency)
{
totDocAmt -= lineDocAmt;
}
}
}
det.Rows.Add(rowDet);
}
DataRow row = mast.NewRow();
row["Oid"] = docId;
row["DocNo"] = tab_mast.Rows[0]["DocNo"].ToString();
row["DocType"] = docType;
row["ChqNo"] = tab_mast.Rows[0]["ChqNo"];
row["AcCode"] = tab_mast.Rows[0]["AcCode"];
row["Remark"] = tab_mast.Rows[0]["Remark"];
row["DocDate"] = SafeValue.SafeDateStr(tab_mast.Rows[0]["DocDate"]);
string partyTo = tab_mast.Rows[0]["PartyTo"].ToString();
if (partyTo.Length > 1)
{
row["PartyTo"] = EzshipHelper.GetPartyName(partyTo);
}
row["Currency"] = currency;
row["ExRate"] = Helper.Safe.AccountEx(exRate);
row["BankName"] = tab_mast.Rows[0]["BankName"];
row["DocAmt"] = Helper.Safe.AccountNz(tab_mast.Rows[0]["DocAmt"]); //totDocAmt.ToString("#,##0.00");
row["LocAmt"] = Helper.Safe.AccountNz(tab_mast.Rows[0]["LocAmt"]); //totLocAmt.ToString("#,##0.00");
row["TotDocAmt"] = Helper.Safe.AccountNz(totDocAmt); //totDocAmt.ToString("#,##0.00");
row["TotLocAmt"] = Helper.Safe.AccountNz(totLocAmt); //totLocAmt.ToString("#,##0.00");
row["UserId"] = tab_mast.Rows[0]["UserId"];
mast.Rows.Add(row);
}
}
catch
{
}
DataSet ds = new DataSet();
ds.Tables.Add(mast);
ds.Tables.Add(det);
DataRelation r = new DataRelation("", mast.Columns["Oid"], det.Columns["Oid"]);
ds.Relations.Add(r);
return(ds);
}
public static DataSet DsApVoucher(string docId)
{
string sqlMast = string.Format(@"SELECT SequenceId, AcCode, DocType, DocNo, DocDate,SupplierBillNo, ChqNo, ChqDate, PartyTo, OtherPartyName, CurrencyId, ExRate, Description, DocAmt, LocAmt,UserId,Term
FROM XAApPayable WHERE(SequenceId = '{0}')", docId);
string sqlDet = string.Format(@"SELECT AcCode,AcSource, ChgCode, ChgDes1, ChgDes2, ChgDes3, ChgDes4, Currency, ExRate, DocAmt, LocAmt
FROM XAApPayableDet WHERE (DocId = '{0}')", docId);
DataTable mast = new DataTable("mast");
mast.Columns.Add("Oid");
mast.Columns.Add("DocNo");
mast.Columns.Add("DocType");
mast.Columns.Add("SupplierBillNo");
mast.Columns.Add("ChqNo");
mast.Columns.Add("AcCode");
mast.Columns.Add("DocDate");
mast.Columns.Add("PartyTo");
mast.Columns.Add("Currency");
mast.Columns.Add("ExRate");
// mast.Columns.Add("BankName");
mast.Columns.Add("DocAmt");
mast.Columns.Add("LocAmt");
mast.Columns.Add("UserId");
mast.Columns.Add("Remark");
mast.Columns.Add("CompanyName");
DataTable det = new DataTable("Detail");
det.Columns.Add("Oid");
det.Columns.Add("AcCode");
det.Columns.Add("AcSource");
det.Columns.Add("Rmk");
det.Columns.Add("Currency");
det.Columns.Add("ExRate");
det.Columns.Add("DocAmt");
det.Columns.Add("LocAmt");
try
{
DataTable tab_mast = Helper.Sql.List(sqlMast);
DataTable tab_det = Helper.Sql.List(sqlDet);
if (tab_mast.Rows.Count > 0)
{
decimal totAmt = 0;
for (int i = 0; i < tab_det.Rows.Count; i++)
{
string acSource = tab_det.Rows[i]["AcSource"].ToString();
DataRow rowDet = det.NewRow();
rowDet["Oid"] = docId;
rowDet["AcCode"] = tab_det.Rows[i]["AcCode"];
rowDet["AcSource"] = acSource;
rowDet["Rmk"] = tab_det.Rows[i]["ChgDes1"];
rowDet["Currency"] = tab_det.Rows[i]["Currency"];
rowDet["ExRate"] = Helper.Safe.AccountEx(tab_det.Rows[i]["ExRate"]);
rowDet["DocAmt"] = Helper.Safe.AccountNz(tab_det.Rows[i]["DocAmt"]); //, 0).ToString("#,##0.00");
decimal local_ = SafeValue.SafeDecimal(tab_det.Rows[i]["LocAmt"], 0);
if (acSource == "DB")
{
rowDet["LocAmt"] = Helper.Safe.AccountNz(local_);
totAmt += local_;
}
else
{
rowDet["LocAmt"] = Helper.Safe.AccountNz(local_ * -1);
totAmt -= local_;
}
det.Rows.Add(rowDet);
}
DataRow row = mast.NewRow();
row["Oid"] = docId;
row["DocNo"] = tab_mast.Rows[0]["DocNo"].ToString();
row["SupplierBillNo"] = tab_mast.Rows[0]["SupplierBillNo"].ToString();//tab_mast.Rows[0]["DocNo"].ToString();
row["DocType"] = tab_mast.Rows[0]["DocType"];
row["ChqNo"] = tab_mast.Rows[0]["ChqNo"];
row["AcCode"] = tab_mast.Rows[0]["AcCode"];
row["Remark"] = tab_mast.Rows[0]["Description"];
row["DocDate"] = SafeValue.SafeDateStr(tab_mast.Rows[0]["DocDate"]);;//SafeValue.SafeDateStr(tab_mast.Rows[0]["DocDate"]);
string partyTo = tab_mast.Rows[0]["PartyTo"].ToString();
if (partyTo.Length > 3)
{
row["PartyTo"] = EzshipHelper.GetPartyName(partyTo);
}
else
{
row["PartyTo"] = tab_mast.Rows[0]["OtherPartyName"];
}
decimal exRate = SafeValue.SafeDecimal(tab_mast.Rows[0]["ExRate"], 1);
string currency = tab_mast.Rows[0]["CurrencyId"].ToString();
row["Currency"] = currency;
row["ExRate"] = Helper.Safe.AccountEx(exRate);
row["DocAmt"] = totAmt.ToString("#,##0.00");
row["LocAmt"] = totAmt.ToString("#,##0.00");
row["UserId"] = tab_mast.Rows[0]["UserId"];
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
mast.Rows.Add(row);
}
}
catch
{
}
DataSet ds = new DataSet();
ds.Tables.Add(mast);
ds.Tables.Add(det);
DataRelation r = new DataRelation("", mast.Columns["Oid"], det.Columns["Oid"]);
ds.Relations.Add(r);
return(ds);
}
public static DataTable PrintAuthLetter(string orderNo)
{
#region init column
DataTable mast = new DataTable("Mast");
mast.Columns.Add("RefN");
mast.Columns.Add("NowD");
mast.Columns.Add("Cust");
mast.Columns.Add("Vend1");
mast.Columns.Add("BlNo");
mast.Columns.Add("Sn");
mast.Columns.Add("Eta");
mast.Columns.Add("VesVoy");
mast.Columns.Add("ContNo");
mast.Columns.Add("FtSize");
mast.Columns.Add("Port");
mast.Columns.Add("Ms");
mast.Columns.Add("Cr");
mast.Columns.Add("Qty");
mast.Columns.Add("Wt");
mast.Columns.Add("M3");
mast.Columns.Add("SealNo");
mast.Columns.Add("CompanyName");
mast.Columns.Add("User");
mast.Columns.Add("Rmk");
mast.Columns.Add("Pack");
#endregion
string sql = string.Format("SELECT JobOrderNo,JobDate,CustCode,Carrier, Eta, VesselNo, VoyNo, Pol,BookingNo,Pod,PermitNo,PartyCode,Haulier FROM XWJobOrder WHERE (JobOrderNo = '{0}')", orderNo);
DataTable tabOrder = Helper.Sql.List(sql);
if (tabOrder.Rows.Count == 1)
{
string custCode = tabOrder.Rows[0]["CustCode"].ToString();
string sql1 = "select name from xxparty where partyid='" + custCode + "'";
string custName = SafeValue.SafeString(Helper.Sql.One(sql1), custCode);
DateTime jobDate = SafeValue.SafeDate(tabOrder.Rows[0]["JobDate"], DateTime.Today);
string ves = tabOrder.Rows[0]["VesselNo"].ToString();
string voy = tabOrder.Rows[0]["VoyNo"].ToString();
string pol = tabOrder.Rows[0]["Pol"].ToString();
string bl = tabOrder.Rows[0]["BookingNo"].ToString();
string pod = tabOrder.Rows[0]["Pod"].ToString();
string carrier = tabOrder.Rows[0]["Carrier"].ToString();
string permitNo = tabOrder.Rows[0]["PermitNo"].ToString();
string partyCode = tabOrder.Rows[0]["PartyCode"].ToString();
string haulier = tabOrder.Rows[0]["Haulier"].ToString();
sql1 = "select name from [XXPort] where code='" + SafeValue.SafeString(pol, "") + "'";
string port = "";
if (pol == "NA")
{
port = "NA";
}
else
{
port = SafeValue.SafeString(Helper.Sql.One(sql1), pol) + " / " + SafeValue.SafeString(Helper.Sql.One(sql1), pod);
}
string eta = SafeValue.SafeDateStr(tabOrder.Rows[0]["Eta"]);
sql1 = string.Format(@"select Name,CrNo from xxParty where PartyId='{0}'", haulier);
DataTable dt_p = Helper.Sql.List(sql1);
string ms = "";
string cr = "";
if (dt_p.Rows.Count > 0)
{
ms = SafeValue.SafeString(dt_p.Rows[0]["Name"]);
cr = SafeValue.SafeString(dt_p.Rows[0]["CrNo"]);
}
string contNo = "";
string sealNo = "";
int ft20 = 0;
int ft40 = 0;
int ft45 = 0;
int qty = 0;
decimal wt = 0;
decimal m3 = 0;
string ftSize = "";
string ftType = "";
string rmk = "";
string sqlCont = string.Format("SELECT ContNo, SealNo, Ft20, Ft40, Ft45, FtType,Remark FROM XWJobCont WHERE (JobOrder = '{0}')", orderNo);
DataTable tabCont = Helper.Sql.List(sqlCont);
if (tabCont.Rows.Count > 0)
{
for (int i = 0; i < tabCont.Rows.Count; i++)
{
DataRow row1 = tabCont.Rows[i];
contNo = row1["ContNo"].ToString();
sealNo = row1["SealNo"].ToString();
ft20 = SafeValue.SafeInt(row1["Ft20"], 0);
ft40 = SafeValue.SafeInt(row1["Ft40"], 0);
ft45 = SafeValue.SafeInt(row1["Ft45"], 0);
if (ft20 > 0)
{
ftSize = "20";
}
else if (ft40 > 0)
{
ftSize += "40";
}
else if (ft45 > 0)
{
ftSize += "45";
}
ftType = row1["FtType"].ToString();
rmk = row1["Remark"].ToString();
string sql_sum = string.Format("SELECT SUM(QtyOrig) AS Qty, SUM(WeightOrig) AS Wt, SUM(M3Orig) AS M3 FROM XWJobDet WHERE (JobOrder = '{0}') AND (ContNo = '{1}')", orderNo, contNo);
DataTable tab_sum = Helper.Sql.List(sql_sum);
if (tab_sum.Rows.Count == 1)
{
qty = SafeValue.SafeInt(tab_sum.Rows[0]["Qty"], 0);
wt = SafeValue.SafeDecimal(tab_sum.Rows[0]["Wt"], 0);
m3 = SafeValue.SafeDecimal(tab_sum.Rows[0]["M3"], 0);
}
}
}
DataRow row = mast.NewRow();
row["Cust"] = custCode; //custName;
row["Vend1"] = carrier;
row["RefN"] = orderNo;
row["NowD"] = jobDate.ToString("dd/MM/yyyy");
row["Eta"] = eta;
row["VesVoy"] = ves + "/" + voy;
row["ContNo"] = contNo;
row["FtSize"] = ftSize + ftType;
row["Port"] = port;
row["Qty"] = qty;
row["Wt"] = wt.ToString("0.000");
row["M3"] = m3.ToString("0.000");
row["SealNo"] = sealNo;
row["Rmk"] = rmk;
row["BlNo"] = bl;
row["Ms"] = ms;
row["Cr"] = cr;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["Pack"] = "";
row["User"] = HttpContext.Current.User.Identity.Name;
mast.Rows.Add(row);
}
return(mast);
}
public static DataSet DsImpTs_Pre(string orderNo, string userName)
{
DataTable mast = InitTsMastDataTable();
DataTable details = InitTsDetailDataTable();
string sql_Order = string.Format("select Vessel,Voyage,CarrierBkgNo,EtaDate,ClientContact,ClientRefNo,Pol,PartyId,AdditionalRemark,CreateBy,IssuedBy from ctm_job where JobNo='{0}'", orderNo);
DataTable tab_Order = Helper.Sql.List(sql_Order);
if (tab_Order.Rows.Count == 1)
{
DataRow row_Order = tab_Order.Rows[0];
#region tally sheet
DataRow row = mast.NewRow();
row["JobOrderNo"] = orderNo;
row["VesselNo"] = row_Order["Vessel"];
row["BookingNo"] = row_Order["CarrierBkgNo"];
row["VoyNo"] = row_Order["Voyage"];
row["Eta"] = SafeValue.SafeDateStr(row_Order["EtaDate"]);
row["ByWho"] = row_Order["ClientContact"];
row["ImpRefNo"] = row_Order["ClientRefNo"];
string sql = "select name from XXPort where Code='" + SafeValue.SafeString(row_Order["Pol"], "") + "'";
row["PortLoad"] = SafeValue.SafeString(row_Order["Pol"], ""); //SafeValue.SafeString(Helper.Sql.One(sql), "");
sql = "select name from xxparty where partyid='" + row_Order["PartyId"] + "'";
row["CustName"] = row_Order["PartyId"]; //SafeValue.SafeString(Helper.Sql.One(sql), "");
row["Condition"] = row_Order["AdditionalRemark"];
row["UserData1"] = row_Order["CreateBy"];
row["IssueBy"] = row_Order["IssuedBy"];
row["UserName"] = userName;
mast.Rows.Add(row);
string sql_JobDet = string.Format(@"select DoNo,CargoType,ContNo,SealNo,IsDg,BookingNo,HblNo,Marking2
,Qty,UomCode,PackTypeOrig,Volume,Weight,QtyOrig,VolumeOrig,WeightOrig, POD
,Remark1,DgClass,Desc2 from job_house where JobNo='{0}' order by ContNo,LineId,IsDg,DoNo", orderNo);
DataTable tab_Det = Helper.Sql.List(sql_JobDet);
int g0 = 0;
int g1 = 0;
int g2 = 0;
int p = 0;
string lastContNo = "";
bool lastDg = false;
for (int i = 0; i < tab_Det.Rows.Count; i++)
{
DataRow row_Det = tab_Det.Rows[i];
DataRow row1 = details.NewRow();
string dnNo = row_Det["DoNo"].ToString();
string jobType = "";
string contNo = row_Det["ContNo"].ToString();
string sealNo = row_Det["SealNo"].ToString();
bool isDg = SafeValue.SafeBool(row_Det["IsDg"], false);
string dgClass = row_Det["DgClass"].ToString();
string expBkgN = row_Det["BookingNo"].ToString();
string hblN = row_Det["HblNo"].ToString();
string mkg = row_Det["Marking2"].ToString();
string rmk = row_Det["Remark1"].ToString();
string desc2 = row_Det["Desc2"].ToString();
string pod = "";
//int tally1 = SafeValue.SafeInt(row_Det["Tally1"], 0);
//int tally2 = SafeValue.SafeInt(row_Det["Tally2"], 0);
//int tally3 = SafeValue.SafeInt(row_Det["Tally3"], 0);
//int tally4 = SafeValue.SafeInt(row_Det["Tally4"], 0);
//int tally21 = SafeValue.SafeInt(row_Det["Tally21"], 0);
//int tally22 = SafeValue.SafeInt(row_Det["Tally22"], 0);
//int tally23 = SafeValue.SafeInt(row_Det["Tally23"], 0);
//int tally24 = SafeValue.SafeInt(row_Det["Tally24"], 0);
//int tally31 = SafeValue.SafeInt(row_Det["Tally31"], 0);
//int tally32 = SafeValue.SafeInt(row_Det["Tally32"], 0);
//int tally33 = SafeValue.SafeInt(row_Det["Tally33"], 0);
//int tally34 = SafeValue.SafeInt(row_Det["Tally34"], 0);
//int tally41 = SafeValue.SafeInt(row_Det["Tally41"], 0);
//int tally42 = SafeValue.SafeInt(row_Det["Tally42"], 0);
//int tally43 = SafeValue.SafeInt(row_Det["Tally43"], 0);
//int tally44 = SafeValue.SafeInt(row_Det["Tally44"], 0);
//int tally51 = SafeValue.SafeInt(row_Det["Tally51"], 0);
//int tally52 = SafeValue.SafeInt(row_Det["Tally52"], 0);
//int tally53 = SafeValue.SafeInt(row_Det["Tally53"], 0);
//int tally54 = SafeValue.SafeInt(row_Det["Tally54"], 0);
//int tally61 = SafeValue.SafeInt(row_Det["Tally61"], 0);
//int tally62 = SafeValue.SafeInt(row_Det["Tally62"], 0);
//int tally63 = SafeValue.SafeInt(row_Det["Tally63"], 0);
//int tally64 = SafeValue.SafeInt(row_Det["Tally64"], 0);
int qty = SafeValue.SafeInt(row_Det["Qty"], 0);
decimal wt = SafeValue.SafeDecimal(row_Det["Weight"], 0);
decimal m3 = SafeValue.SafeDecimal(row_Det["Volume"], 0);
string pkgType = SafeValue.SafeString(row_Det["UomCode"], "");
string pkgType2 = SafeValue.SafeString(row_Det["PackTypeOrig"], "");
int qtyOrig = SafeValue.SafeInt(row_Det["QtyOrig"], 0);
decimal wtOrig = SafeValue.SafeDecimal(row_Det["WeightOrig"], 0);
decimal m3Orig = SafeValue.SafeDecimal(row_Det["VolumeOrig"], 0);
if ((contNo == lastContNo & isDg == lastDg) || lastContNo == "")
{
g2++;
}
else
{
g0++;
g1 = 1;
g2 = 1;
p = 0;
}
lastContNo = contNo;
lastDg = isDg;
row1["JobOrderNo"] = orderNo;
p++;
row1["LineN"] = p;
row1["PrintGroup"] = string.Format("C{0:00}{1:00}", g0, g1);
row1["PrintSesseion"] = string.Format("C{0:00}", g0);
if (g2 % 6 == 0)
{
g1++;
}
row1["ContNo"] = contNo;
row1["SealNo"] = sealNo;
//string sql_cont = string.Format("select ContNo,SealNo,Ft20,Ft40,Ft45,FtType,scheduleDate,ScheduleTime,ActualDate,ActualTime,TallyClerk,FlDriver from xwjobcont where JobOrder='{0}' and ContNo='{1}'", orderNo, contNo);
//DataTable tab_cont = Helper.Sql.List(sql_cont);
//if (tab_cont.Rows.Count > 0)
//{
// row1["SealNo"] = SafeValue.SafeString(tab_cont.Rows[0]["SealNo"], "");
// row1["FtType"] = SafeValue.SafeString(tab_cont.Rows[0]["FtType"], "");
// row1["SchDate"] = SafeValue.SafeDateStr(tab_cont.Rows[0]["ScheduleDate"]);
// row1["ComplDate"] = SafeValue.SafeDateStr(tab_cont.Rows[0]["ActualDate"]);
// string time = SafeValue.SafeString(tab_cont.Rows[0]["ScheduleTime"], "");
// string time1 = SafeValue.SafeString(tab_cont.Rows[0]["ActualTime"], "");
// if (time.Trim().Length > 0)
// {
// row1["SchTime"] = time;
// }
// if (time1.Trim().Length > 0)
// row1["ComplTime"] = time1;
// string size = "";
// if (SafeValue.SafeInt(tab_cont.Rows[0]["Ft20"], 0) > 0)
// size = "20";
// else if (SafeValue.SafeInt(tab_cont.Rows[0]["Ft40"], 0) > 0)
// size = "40";
// else if (SafeValue.SafeInt(tab_cont.Rows[0]["Ft45"], 0) > 0)
// size = "45";
// row1["Size"] = size;
// row1["TallyClerk"] = SafeValue.SafeString(tab_cont.Rows[0]["TallyClerk"], "");
// row1["Driver"] = SafeValue.SafeString(tab_cont.Rows[0]["FlDriver"], "");
//}
row1["DnNO"] = dnNo.Trim();
row1["ExpBkgN"] = expBkgN;
row1["HblN"] = hblN;
row1["TotMarking"] = mkg;
//row1["Tally1"] = tally1.ToString("#");
//row1["Tally2"] = tally2.ToString("#");
//row1["Tally3"] = tally3.ToString("#");
//row1["Tally4"] = tally4.ToString("#");
//row1["Tally21"] = tally21.ToString("#");
//row1["Tally22"] = tally22.ToString("#");
//row1["Tally23"] = tally23.ToString("#");
//row1["Tally24"] = tally24.ToString("#");
//row1["Tally31"] = tally31.ToString("#");
//row1["Tally32"] = tally32.ToString("#");
//row1["Tally33"] = tally33.ToString("#");
//row1["Tally34"] = tally34.ToString("#");
//row1["Tally41"] = tally41.ToString("#");
//row1["Tally42"] = tally42.ToString("#");
//row1["Tally43"] = tally43.ToString("#");
//row1["Tally44"] = tally44.ToString("#");
//row1["Tally51"] = tally51.ToString("#");
//row1["Tally52"] = tally52.ToString("#");
//row1["Tally53"] = tally53.ToString("#");
//row1["Tally54"] = tally54.ToString("#");
//row1["Tally61"] = tally61.ToString("#");
//row1["Tally62"] = tally62.ToString("#");
//row1["Tally63"] = tally63.ToString("#");
//row1["Tally64"] = tally64.ToString("#");
row1["TotQty"] = qty.ToString("#");
row1["PackType"] = pkgType;
row1["PackTypeOrig"] = pkgType2;
row1["M3"] = m3.ToString("#,##0.000");
row1["Weight"] = wt.ToString("#,##0.000");
row1["OrigQty"] = qtyOrig.ToString("#");
row1["OrigM3"] = m3Orig.ToString("#,##0.000");
row1["OrigWeight"] = wtOrig.ToString("#,##0.000");
if (qtyOrig != qty)
{
row1["DiffQty"] = qty - qtyOrig;
}
if (m3Orig != m3)
{
row1["DiffM3"] = m3 - m3Orig;
}
if (wtOrig != wt)
{
row1["DiffWeight"] = wt - wtOrig;
}
row1["TotRemark"] = rmk;
row1["LocalRemark"] = "";
if (isDg)
{
if (dgClass.Trim().Length < 1)
{
row1["DgClass"] = "DG Cargo";
}
else
{
row1["DgClass"] = "DG Class:\n" + dgClass;
}
}
//row1["TotDesc"] = desc2.Trim();
row1["POD"] = pod;
if (jobType.ToUpper() == "T")
{
if (desc2.Trim().Length > 23)
{
row1["TotDesc"] = desc2.Trim().Substring(desc2.Trim().Length - 23).Trim().Replace("\n", "") + "\n" + rmk.Trim();
}
else
{
row1["TotDesc"] = desc2.Trim() + "\n" + rmk.Trim();
}
// row1["TotDesc"] += "\n\nM3:" + SafeValue.SafeDecimal(row_Det["M3"], 0).ToString("0.000");
}
else
{
row1["TotDesc"] = rmk;
}
details.Rows.Add(row1);
}
#endregion
}
DataSet set = new DataSet();
set.Tables.Add(mast);
set.Tables.Add(details);
set.Relations.Add("", mast.Columns["JobOrderNo"], details.Columns["JobOrderNo"]);
return(set);
}
public void SaveExcel()
{
DateTime now = DateTime.Today;
string pathTemp = HttpContext.Current.Server.MapPath("~/files/templete/StockBalance.xls").ToLower();
string dateTo = "";
if (txt_Date.Value != null)
{
dateTo = txt_Date.Date.ToString("yyyy-MM-dd");
}
string file = string.Format(@"StockBalance for {0:dd} {0:MMMM} {0:yyyy}.xls", now).ToLower();
string path = MapPath("~/files/excel/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string pathUrl = string.Format("~/files/excel/{0}", file);
string pathOpen = HttpContext.Current.Server.MapPath(pathUrl).ToLower();
using (FileStream input = File.OpenRead(pathTemp), output = File.OpenWrite(pathOpen))
{
int read = -1;
byte[] buffer = new byte[4096];
while (read != 0)
{
read = input.Read(buffer, 0, buffer.Length);
output.Write(buffer, 0, read);
}
}
License lic = new License();
lic.SetLicense(HttpContext.Current.Server.MapPath(@"~\bin\License.lic"));
Workbook workbook = new Workbook();
workbook.Open(pathOpen);
Worksheet sheet0 = workbook.Worksheets[0];
Cells c1 = sheet0.Cells;
int r = 1;
c1[r, 2].PutValue(txt_JobNo.Text);
c1[r, 4].PutValue(txt_LotNo.Text);
c1[r, 7].PutValue(txt_HblNo.Text);
c1[r, 9].PutValue(txt_SKULine_Product.Text);
c1[r, 11].PutValue(txt_Date.Date.ToString("yyyy-MM-dd"));
r = 3;
c1[r, 2].PutValue(SafeValue.SafeString(cmb_WareHouse.Value));
c1[r, 4].PutValue(SafeValue.SafeString(txt_Location.Text));
c1[r, 7].PutValue(txt_CustName.Text);
r = 8;
//set style
Aspose.Cells.Style style0 = workbook.Styles[workbook.Styles.Add()];
//style0.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#B09067");
//style0.ForegroundColor = Color.FromName("#B09067");
style0.ForegroundColor = Color.Yellow;
style0.Pattern = BackgroundType.Solid;
//c1[t, 2].SetStyle(style0);
Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];
style1.Font.IsBold = false;
style1.Font.Size = 12;
style1.HorizontalAlignment = TextAlignmentType.Center;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//Select Data
int cnt = 0;
#region Data
DataTable dt = GetData();
int Colnum = dt.Columns.Count; //Cell Colnum number
int Rownum = dt.Rows.Count; //Cell Row number
DataRow dr = null;
DateTime today = DateTime.Now;
for (int i = 0; i < dt.Rows.Count; i++)
{
c1[r, 0].PutValue(R.Text(i + 1));
c1[r, 1].PutValue(R.Text(dt.Rows[i]["PartyName"]));
c1[r, 2].PutValue(R.Text(dt.Rows[i]["JobNo"]));
c1[r, 3].PutValue(R.Text(dt.Rows[i]["WareHouseCode"]));
c1[r, 4].PutValue(R.Text(dt.Rows[i]["WhsType"]));
c1[r, 5].PutValue(R.Text(dt.Rows[i]["PermitNo"]));
c1[r, 6].PutValue(R.Text(dt.Rows[i]["Location"]));
c1[r, 7].PutValue(R.Text(dt.Rows[i]["BookingNo"]));
c1[r, 8].PutValue(R.Text(dt.Rows[i]["HblNo"]));
c1[r, 9].PutValue(R.Text(dt.Rows[i]["ContNo"]));
c1[r, 10].PutValue(R.Text(dt.Rows[i]["OpsType"]));
c1[r, 11].PutValue(R.Text(SafeValue.SafeDateStr(dt.Rows[i]["StockDate"])));
c1[r, 12].PutValue(R.Text(dt.Rows[i]["SkuCode"]));
//Stock
//c1[r, 10].PutValue(R.Text(dt.Rows[i]["QtyOrig"]));
//c1[r, 11].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
//c1[r, 12].PutValue(R.Text(dt.Rows[i]["WeightOrig"]));
//c1[r, 13].PutValue(R.Text(dt.Rows[i]["VolumeOrig"]));
//c1[r, 14].PutValue(R.Text(dt.Rows[i]["PackQty"]));
//c1[r, 15].PutValue(R.Text(dt.Rows[i]["PackUom"]));
//Balance
c1[r, 13].PutValue(R.Text(dt.Rows[i]["BalQty"]));
c1[r, 14].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 15].PutValue(R.Text(BalanceWeight(dt.Rows[i]["LineId"])));
c1[r, 16].PutValue(R.Text(BalanceVolume(dt.Rows[i]["LineId"])));
c1[r, 17].PutValue(R.Text(dt.Rows[i]["SkuQty"]));
c1[r, 18].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 19].PutValue(R.Text(dt.Rows[i]["Marking1"]));
c1[r, 20].PutValue(R.Text(dt.Rows[i]["Marking2"]));
c1[r, 21].PutValue(R.Text(dt.Rows[i]["Remark1"]));
dr = dt.Rows[i];
for (int j = 0; j <= 18; j++)
{
c1[r, j].SetStyle(style1);
}
cnt = i + 1;
r++;
}
#endregion
workbook.Save(pathOpen, FileFormatType.Excel2003);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, FileFormatType.Excel2003);
byte[] bt = ms.GetBuffer();
try
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Length", bt.Length.ToString());
Response.AddHeader("Content-Disposition", "attachment; filename=" + file);
Response.BinaryWrite(bt);
}
catch (Exception exc)
{
throw new Exception(exc.Message + "/" + exc.StackTrace);
}
ms.Dispose();
}
public static DataSet DsDeferredIncome(DateTime d1, DateTime d2, string userId)
{
DataSet set = new DataSet();
DataTable mast = new DataTable("Mast");
mast.Columns.Add("Title");
mast.Columns.Add("DatePeriod");
mast.Columns.Add("UserId");
mast.Columns.Add("Index");
mast.Columns.Add("DocNo");
mast.Columns.Add("DocType");
mast.Columns.Add("DocDate");
mast.Columns.Add("Eta");
mast.Columns.Add("JobType");
mast.Columns.Add("PartyId");
mast.Columns.Add("MastAcCode");
mast.Columns.Add("MastAcDesc");
mast.Columns.Add("PartyName");
mast.Columns.Add("AcCode");
mast.Columns.Add("AcDesc");
mast.Columns.Add("LineAmt");
string sql = "";
sql = string.Format(@"SELECT SequenceId, AcCode, AcSource, DocNo, DocType, DocDate, MastClass, MastType, Eta, PartyTo, DocAmt, LocAmt
FROM XAArInvoice where Eta>='{1}' AND DocDate>='{0}' and DocDate<'{1}' ", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"));
sql += " order by DocType,DocNo";
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectString"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
// Call Read before accessing data.
int index = 1;
while (reader.Read())
{
string sequenceId = reader["SequenceId"].ToString();
string title = "DEFERRED INCOME REPORT";
string sql_det = string.Format("SELECT AcCode, Qty, Price, ExRate, Gst, GstAmt, DocAmt, LocAmt FROM XAArInvoiceDet WHERE (DocId = '{0}')", sequenceId);
DataTable tab = Helper.Sql.List(sql_det);
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow row1 = mast.NewRow();
row1["Title"] = title;
row1["DatePeriod"] = string.Format("Doc Date :{0} To :{1}", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"));
row1["UserId"] = userId;
row1["Index"] = index;
row1["DocNo"] = reader["DocNo"].ToString();
row1["DocType"] = reader["DocType"].ToString();
row1["DocDate"] = SafeValue.SafeDateStr(reader["DocDate"]);
row1["Eta"] = SafeValue.SafeDateStr(reader["Eta"]);
string partyId1 = reader["PartyTo"].ToString();
string partyName1 = EzshipHelper.GetPartyName(partyId1);
row1["PartyId"] = partyId1;
row1["PartyName"] = partyName1;
row1["MastAcCode"] = reader["AcCode"].ToString();
row1["MastAcDesc"] = GetObj("Select AcDesc from XXChartAcc where Code='" + reader["AcCode"] + "'");
row1["AcCode"] = tab.Rows[i]["AcCode"].ToString();
row1["AcDesc"] = GetObj("Select AcDesc from XXChartAcc where Code='" + tab.Rows[i]["AcCode"] + "'");
decimal amt = SafeValue.ChinaRound(SafeValue.SafeDecimal(tab.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(tab.Rows[i]["Price"], 0) * SafeValue.SafeDecimal(tab.Rows[i]["ExRate"], 1), 2);
row1["LineAmt"] = amt.ToString("#,##.00");
mast.Rows.Add(row1);
index++;
}
}
reader.Close();
reader.Dispose();
set.Tables.Add(mast);
return(set);
}
public static DataSet DsApDocListing_Detail(DateTime d1, DateTime d2, string partyTo, string docType, string userId)
{
DataSet set = new DataSet();
DataTable mast = new DataTable("Mast");
mast.Columns.Add("Title");
mast.Columns.Add("DatePeriod");
mast.Columns.Add("UserId");
mast.Columns.Add("Index");
mast.Columns.Add("DocNo");
mast.Columns.Add("DocType");
mast.Columns.Add("DocDate");
mast.Columns.Add("DueDate");
mast.Columns.Add("PartyId");
mast.Columns.Add("PartyName");
mast.Columns.Add("ChqNo");
mast.Columns.Add("Currency");
mast.Columns.Add("ExRate");
mast.Columns.Add("LocAmt");
mast.Columns.Add("AcCode");
mast.Columns.Add("AcDesc");
mast.Columns.Add("DbAmt");
mast.Columns.Add("CrAmt");
mast.Columns.Add("LocDbAmt");
mast.Columns.Add("LocCrAmt");
mast.Columns.Add("DbAmt1");
mast.Columns.Add("CrAmt1");
mast.Columns.Add("LocDbAmt1");
mast.Columns.Add("LocCrAmt1");
string sql = "";
if (docType == "PL" || docType == "SC" || docType == "SD")
{
sql = string.Format(@"SELECT SequenceId, AcCode, AcSource, SupplierBillNo AS DocNo, DocType, DocDate AS DocDate, PartyTo, CurrencyId, ExRate, '' AS ChqNo, DocAmt, LocAmt
FROM XAApPayable where DocDate>='{0}' and DocDate<'{1}' and DocType='{2}'", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"), docType);
if (partyTo.Length > 0 && "null" != partyTo)
{
sql += " and PartyTo'" + partyTo + "'";
}
sql += " order by PartyTo,DocType,DocNo";
}
else if (docType == "PS" || docType == "SR")
{
sql = string.Format(@"SELECT SequenceId, AcCode, AcSource, DocNo, DocType, DocDate, PartyTo, CurrencyId, ExRate, ChqNo, DocAmt, LocAmt
FROM XAApPayment where DocDate>='{0}' and DocDate<'{1}' and DocType='{2}'", d1.ToString("yyyy-MM-dd"), d2.AddDays(1).ToString("yyyy-MM-dd"), docType);
if (partyTo.Length > 0 && "null" != partyTo)
{
sql += " and PartyTo'" + partyTo + "'";
}
sql += " order by PartyTo,DocType,DocNo";
}
string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnectString"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataReader reader = cmd.ExecuteReader();
// Call Read before accessing data.
int index = 1;
while (reader.Read())
{
string sequenceId = reader["SequenceId"].ToString();
string sql_det = "";
string title = "AP SUPPLIER INVOICE LISTING";
if (docType == "PL")
{
sql_det = string.Format("SELECT AcCode, AcSource, DocAmt, LocAmt FROM XAApPayableDet where DocId='{0}'", sequenceId);
}
else if (docType == "SD")
{
title = "AP SUPPLIER DEBIT NOTE LISTING";
sql_det = string.Format("SELECT AcCode, AcSource, DocAmt, LocAmt FROM XAApPayableDet where DocId='{0}'", sequenceId);
}
else if (docType == "SC")
{
title = "AP SUPPLIER CREDIT NOTE LISTING";
sql_det = string.Format("SELECT AcCode, AcSource, DocAmt, LocAmt FROM XAApPayableDet where DocId='{0}'", sequenceId);
}
else if (docType == "PS")
{
title = "AP SUPPLIER PAYMENT LISTING";
sql_det = string.Format("SELECT AcCode, AcSource, DocAmt, LocAmt FROM XAApPaymentDet WHERE PayId = '{0}'", sequenceId);
}
else if (docType == "PC")
{
title = "AP SUPPLIER REFUND LISTING";
sql_det = string.Format("SELECT AcCode, AcSource, DocAmt, LocAmt FROM XAApPaymentDet WHERE PayId = '{0}'", sequenceId);
}
decimal locAmt = SafeValue.SafeDecimal(reader["LocAmt"], 0);
decimal docAmt = SafeValue.SafeDecimal(reader["DocAmt"], 0);
DataTable tab = Helper.Sql.List(sql_det);
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow row1 = mast.NewRow();
row1["Title"] = title;
row1["DatePeriod"] = string.Format("Doc Date :{0} To :{1}", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"));
row1["UserId"] = userId;
row1["Index"] = index;
row1["DocNo"] = reader["DocNo"].ToString();
row1["DocType"] = docType;
row1["DocDate"] = SafeValue.SafeDateStr(reader["DocDate"]);
row1["DueDate"] = "";
if (docType == "IV" || docType == "CN" || docType == "DN")
{
row1["DueDate"] = SafeValue.SafeDateStr(reader["DocDueDate"]);
}
string partyId1 = reader["PartyTo"].ToString();
string partyName1 = EzshipHelper.GetPartyName(partyId1);
row1["PartyId"] = partyId1;
row1["PartyName"] = partyName1;
row1["ChqNo"] = reader["ChqNo"].ToString();
row1["Currency"] = reader["CurrencyId"].ToString();
row1["ExRate"] = SafeValue.SafeDecimal(reader["ExRate"], 1).ToString("0.000");
row1["LocAmt"] = 0;
row1["AcCode"] = tab.Rows[i]["AcCode"].ToString();
row1["DbAmt"] = 0;
row1["CrAmt"] = 0;
row1["LocDbAmt"] = 0;
row1["LocCrAmt"] = 0;
string acDorc1 = tab.Rows[i]["AcSource"].ToString();
decimal detDocAmt = SafeValue.SafeDecimal(tab.Rows[i]["DocAmt"], 0);
decimal detLocAmt = SafeValue.SafeDecimal(tab.Rows[i]["LocAmt"], 0);
if (acDorc1 == "DB")
{
row1["DbAmt"] = detDocAmt;
row1["LocDbAmt"] = detLocAmt;
if (docAmt > 0)
{
row1["DbAmt1"] = detDocAmt.ToString("#,##0.00");
row1["LocDbAmt1"] = detLocAmt.ToString("#,##0.00");
}
}
else
{
row1["CrAmt"] = SafeValue.SafeDecimal(tab.Rows[i]["DocAmt"], 0);
row1["LocCrAmt"] = SafeValue.SafeDecimal(tab.Rows[i]["LocAmt"], 0);
if (docAmt > 0)
{
row1["CrAmt1"] = detDocAmt.ToString("#,##0.00");
row1["LocCrAmt1"] = detLocAmt.ToString("#,##0.00");
}
}
mast.Rows.Add(row1);
if (i == 0)
{
DataRow row = mast.NewRow();
row["Title"] = title;
row["DatePeriod"] = string.Format("Doc Date :{0} To :{1}", d1.ToString("dd/MM/yyyy"), d2.ToString("dd/MM/yyyy"));
row["UserId"] = userId;
row["Index"] = index;
row["DocNo"] = reader["DocNo"].ToString();
row["DocType"] = docType;
row["DocDate"] = SafeValue.SafeDateStr(reader["DocDate"]);
row["DueDate"] = "";
if (docType == "IV" || docType == "CN" || docType == "DN")
{
row["DueDate"] = SafeValue.SafeDateStr(reader["DocDueDate"]);
}
string partyId = reader["PartyTo"].ToString();
string partyName = EzshipHelper.GetPartyName(partyId);
row["PartyId"] = partyId;
row["PartyName"] = partyName;
row["ChqNo"] = reader["ChqNo"].ToString();
row["Currency"] = reader["CurrencyId"].ToString();
row["ExRate"] = SafeValue.SafeDecimal(reader["ExRate"], 1).ToString("0.000");
row["LocAmt"] = locAmt.ToString("#,##0.00");
row["AcCode"] = reader["AcCode"].ToString();
row["DbAmt"] = 0;
row["CrAmt"] = 0;
row["LocDbAmt"] = 0;
row["LocCrAmt"] = 0;
string acDorc = reader["AcSource"].ToString();
if (acDorc == "DB")
{
row["DbAmt"] = docAmt;
row["LocDbAmt"] = locAmt;
if (docAmt > 0)
{
row["DbAmt1"] = docAmt.ToString("#,##0.00");
row["LocDbAmt1"] = locAmt.ToString("#,##0.00");
}
}
else
{
row["CrAmt"] = docAmt;
row["LocCrAmt"] = locAmt;
if (docAmt > 0)
{
row["CrAmt1"] = docAmt.ToString("#,##0.00");
row["LocCrAmt1"] = locAmt.ToString("#,##0.00");
}
}
mast.Rows.Add(row);
}
else if (i == tab.Rows.Count - 1)
{
index++;
}
}
}
reader.Close();
reader.Dispose();
DataSetHelper help = new DataSetHelper();
DataTable tab1 = help.SelectGroupByInto("table1", mast, "AcCode,sum(DbAmt) DbAmt,sum(CrAmt) CrAmt,sum(LocDbAmt) LocDbAmt,sum(LocCrAmt) LocCrAmt", "", "AcCode");
DataTable rptDet = new DataTable("Detail");
rptDet.Columns.Add("AcCode");
rptDet.Columns.Add("AcDesc");
rptDet.Columns.Add("DbAmt");
rptDet.Columns.Add("CrAmt");
rptDet.Columns.Add("LocDbAmt");
rptDet.Columns.Add("LocCrAmt");
for (int i = 0; i < tab1.Rows.Count; i++)
{
DataRow rptRow = rptDet.NewRow();
rptRow["AcCode"] = tab1.Rows[i]["AcCode"];
rptRow["AcDesc"] = GetObj("Select AcDesc from XXChartAcc where Code='" + tab1.Rows[i]["AcCode"] + "'");
rptRow["DbAmt"] = tab1.Rows[i]["DbAmt"];
rptRow["CrAmt"] = tab1.Rows[i]["CrAmt"];
rptRow["LocDbAmt"] = tab1.Rows[i]["LocDbAmt"];
rptRow["LocCrAmt"] = tab1.Rows[i]["LocCrAmt"];
rptDet.Rows.Add(rptRow);
}
set.Tables.Add(mast);
set.Tables.Add(rptDet);
return(set);
}
public void SaveExcel()
{
DateTime now = DateTime.Today;
string pathTemp = HttpContext.Current.Server.MapPath("~/files/templete/StockMove.xls").ToLower();
string file = string.Format(@"StockMove for {0:dd} {0:MMMM} {0:yyyy}.xls", now).ToLower();
string path = MapPath("~/files/excel/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string pathUrl = string.Format("~/files/excel/{0}", file);
string pathOpen = HttpContext.Current.Server.MapPath(pathUrl).ToLower();
using (FileStream input = File.OpenRead(pathTemp), output = File.OpenWrite(pathOpen))
{
int read = -1;
byte[] buffer = new byte[4096];
while (read != 0)
{
read = input.Read(buffer, 0, buffer.Length);
output.Write(buffer, 0, read);
}
}
License lic = new License();
lic.SetLicense(HttpContext.Current.Server.MapPath(@"~\bin\License.lic"));
Workbook workbook = new Workbook();
workbook.Open(pathOpen);
Worksheet sheet0 = workbook.Worksheets[0];
Cells c1 = sheet0.Cells;
int r = 1;
c1[r, 1].PutValue("Lot No");
c1[r, 2].PutValue(txt_LotNo.Text);
c1[r, 3].PutValue("Hbl No");
c1[r, 4].PutValue(txt_HblNo.Text);
c1[r, 5].PutValue("Cont No");
c1[r, 6].PutValue(txt_ContNo.Text);
c1[r, 7].PutValue("SKU");
c1[r, 8].PutValue(txt_SKULine_Product.Text);
c1[r, 9].PutValue("Mft LotDate");
c1[r, 10].PutValue(SafeValue.SafeDateStr(txt_from.Date));
c1[r, 11].PutValue("To");
c1[r, 12].PutValue(SafeValue.SafeDateStr(txt_end.Date));
r = 3;
c1[r, 1].PutValue("WareHouse");
c1[r, 2].PutValue(SafeValue.SafeString(cmb_WareHouse.Value));
c1[r, 3].PutValue("Location");
c1[r, 4].PutValue(txt_Location.Text);
c1[r, 5].PutValue("Customer");
c1[r, 6].PutValue(txt_CustName.Text);
c1.Merge(r, 6, 1, 3);
c1[r, 9].PutValue("Mft Expiry Date");
c1[r, 10].PutValue(SafeValue.SafeDateStr(date_From_ExpiryDate.Date));
c1[r, 11].PutValue("To");
c1[r, 12].PutValue(SafeValue.SafeDateStr(date_To_ExpiryDate.Date));
r = 5;
c1[r, 1].PutValue("On Hold");
c1[r, 2].PutValue(cmb_OnHold.Value);
c1[r, 3].PutValue("Mft LotNo");
c1[r, 4].PutValue(txt_Mft_LotNo.Text);
c1[r, 5].PutValue("Direction");
c1[r, 6].PutValue(SafeValue.SafeString(cmb_Direction.Value));
c1[r, 7].PutValue("Part No");
c1[r, 8].PutValue(btn_PartNo.Text);
c1[r, 9].PutValue("JobDate");
c1[r, 10].PutValue(date_JobDate.Date.ToString("yyyy-MM-dd"));
c1[r, 11].PutValue("To");
c1[r, 12].PutValue(date_to_JobDate.Date.ToString("yyyy-MM-dd"));
//set style
Aspose.Cells.Style style0 = workbook.Styles[workbook.Styles.Add()];
//style0.ForegroundColor = System.Drawing.ColorTranslator.FromHtml("#B09067");
//style0.ForegroundColor = Color.FromName("#B09067");
style0.ForegroundColor = Color.Yellow;
style0.Pattern = BackgroundType.Solid;
//c1[t, 2].SetStyle(style0);
Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];
style1.Font.IsBold = false;
style1.Font.Size = 12;
style1.HorizontalAlignment = TextAlignmentType.Center;
style1.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;
style1.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style1.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
Aspose.Cells.Style style2 = workbook.Styles[workbook.Styles.Add()];
style2.Font.IsBold = false;
style2.Font.Size = 12;
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
//Select Data
int cnt = 0;
r = 10;
#region Data
string date1From = "";
string date1To = "";
string date2From = "";
string date2To = "";
string jobDateFrom = "";
string jobDateTo = "";
if (txt_from.Value != null && txt_end != null)
{
date1From = txt_from.Date.ToString("yyyy-MM-dd");
date1To = txt_end.Date.ToString("yyyy-MM-dd");
}
if (date_From_ExpiryDate.Value != null && date_To_ExpiryDate != null)
{
date2From = date_From_ExpiryDate.Date.ToString("yyyy-MM-dd");
date2To = date_To_ExpiryDate.Date.ToString("yyyy-MM-dd");
}
if (date_JobDate.Value != null && date_to_JobDate.Value != null)
{
jobDateFrom = date_JobDate.Date.ToString("yyyy-MM-dd");
jobDateTo = date_to_JobDate.Date.ToString("yyyy-MM-dd");
}
DataTable dt = GetData(this.txt_CustId.Text, date1From, date1To, date2From, date2To, txt_HblNo.Text, txt_LotNo.Text, txt_SKULine_Product.Text, cmb_WareHouse.Text, txt_Location.Text, txt_ContNo.Text
, txt_Mft_LotNo.Text, btn_PartNo.Text, SafeValue.SafeString(cmb_OnHold.Value), SafeValue.SafeString(cmb_Direction.Value), jobDateFrom, jobDateTo);
int Colnum = dt.Columns.Count; //Cell Colnum number
int Rownum = dt.Rows.Count; //Cell Row number
DataRow dr = null;
DateTime today = DateTime.Now;
int n = 0;
decimal handQty = 0;
decimal skuQty = 0;
string lastRefNo = "";
string lastContNo = "";
string lastBookingNo = "";
string lastSkuCode = "";
string lastHblNo = "";
int lastLineId = 0;
decimal inQty = 0;
decimal outQty = 0;
decimal skuIn = 0;
decimal skuOut = 0;
decimal inWeight = 0;
decimal outWeight = 0;
decimal inVolume = 0;
decimal outVolume = 0;
decimal handWeight = 0;
decimal handVolume = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
int no = SafeValue.SafeInt(dt.Rows[i]["No"], 0);
string type = SafeValue.SafeString(dt.Rows[i]["CargoType"]);
string bookingNo = SafeValue.SafeString(dt.Rows[i]["BookingNo"]);
int lineId = SafeValue.SafeInt(dt.Rows[i]["LineId"], 0);
string refNo = SafeValue.SafeString(dt.Rows[i]["RefNo"]);
string hblNo = SafeValue.SafeString(dt.Rows[i]["HblNo"]);
string skuCode = SafeValue.SafeString(dt.Rows[i]["ActualItem"]);
if (type == "IN")
{
inQty = SafeValue.SafeDecimal(dt.Rows[i]["QtyOrig"]);
skuIn = SafeValue.SafeDecimal(dt.Rows[i]["PackQty"]);
inWeight = SafeValue.SafeDecimal(dt.Rows[i]["WeightOrig"]);
inVolume = SafeValue.SafeDecimal(dt.Rows[i]["VolumeOrig"]);
}
else
{
outQty = SafeValue.SafeDecimal(dt.Rows[i]["QtyOrig"]);
skuOut = SafeValue.SafeDecimal(dt.Rows[i]["PackQty"]);
outWeight = SafeValue.SafeDecimal(dt.Rows[i]["WeightOrig"]);
outVolume = SafeValue.SafeDecimal(dt.Rows[i]["VolumeOrig"]);
}
string contNo = SafeValue.SafeString(dt.Rows[i]["ContNo"]);
if (lineId == lastLineId)
{
if (n == 0)
{
handQty = inQty - outQty;
skuQty = skuIn - skuOut;
handWeight = inWeight - outWeight;
handVolume = inVolume - outVolume;
}
else
{
handQty = handQty - outQty;
skuQty = skuQty - skuOut;
handWeight = handWeight - outWeight;
handVolume = handVolume - outVolume;
}
c1[r, 0].PutValue(R.Text(i + 1));
c1[r, 1].PutValue(R.Text(dt.Rows[i]["JobNo"]));
c1[r, 2].PutValue(R.Text(Helper.Safe.SafeDateStr(dt.Rows[i]["JobDate"])));
c1[r, 3].PutValue(R.Text(dt.Rows[i]["ClientId"]));
c1[r, 4].PutValue(R.Text(dt.Rows[i]["CargoType"]));
c1[r, 5].PutValue(R.Text(dt.Rows[i]["WareHouseCode"]));
c1[r, 6].PutValue("");
c1[r, 7].PutValue("");
c1[r, 8].PutValue(R.Text(dt.Rows[i]["Location"]));
c1[r, 9].PutValue(R.Text(dt.Rows[i]["BookingNo"]));
c1[r, 10].PutValue(R.Text(dt.Rows[i]["HblNo"]));
c1[r, 11].PutValue(R.Text(dt.Rows[i]["ContNo"]));
c1[r, 12].PutValue(R.Text(dt.Rows[i]["CargoType"]));
c1[r, 13].PutValue(R.Text(dt.Rows[i]["ActualItem"]));
c1[r, 14].PutValue(R.Text(""));
c1[r, 15].PutValue(R.Text(""));
c1[r, 16].PutValue(R.Text(""));
c1[r, 17].PutValue(R.Text(""));
c1[r, 18].PutValue(R.Text(""));
c1[r, 19].PutValue(R.Text(""));
c1[r, 20].PutValue(R.Text(outQty));
c1[r, 21].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 22].PutValue(R.Text(outWeight));
c1[r, 23].PutValue(R.Text(outVolume));
c1[r, 24].PutValue(R.Text(skuOut));
c1[r, 25].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 26].PutValue(R.Text(handQty));
c1[r, 27].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 28].PutValue(R.Text(handWeight));
c1[r, 29].PutValue(R.Text(handVolume));
c1[r, 30].PutValue(R.Text(skuQty));
c1[r, 31].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 32].PutValue(R.Text(dt.Rows[i]["Marking1"]));
c1[r, 33].PutValue(R.Text(dt.Rows[i]["Marking2"]));
c1[r, 34].PutValue(R.Text(dt.Rows[i]["Remark1"]));
n++;
for (int j = 0; j <= 32; j++)
{
c1[r, j].SetStyle(style2);
}
}
else
{
n = 0;
handQty = inQty;
skuQty = skuIn;
handWeight = inWeight;
handVolume = inVolume;
lastRefNo = SafeValue.SafeString(dt.Rows[i]["RefNo"]);
c1[r, 0].PutValue(R.Text(i + 1));
c1[r, 1].PutValue(R.Text(dt.Rows[i]["JobNo"]));
c1[r, 2].PutValue(R.Text(Helper.Safe.SafeDateStr(dt.Rows[i]["JobDate"])));
c1[r, 3].PutValue(R.Text(dt.Rows[i]["ClientId"]));
c1[r, 4].PutValue(R.Text(dt.Rows[i]["CargoType"]));
c1[r, 5].PutValue(R.Text(dt.Rows[i]["WareHouseCode"]));
c1[r, 6].PutValue(R.Text(dt.Rows[i]["WhsType"]));
c1[r, 7].PutValue(R.Text(dt.Rows[i]["PermitNo"]));
c1[r, 8].PutValue(R.Text(dt.Rows[i]["Location"]));
c1[r, 9].PutValue(R.Text(dt.Rows[i]["BookingNo"]));
c1[r, 10].PutValue(R.Text(dt.Rows[i]["HblNo"]));
c1[r, 11].PutValue(R.Text(dt.Rows[i]["ContNo"]));
c1[r, 12].PutValue(R.Text(dt.Rows[i]["CargoType"]));
c1[r, 13].PutValue(R.Text(dt.Rows[i]["ActualItem"]));
c1[r, 14].PutValue(R.Text(inQty));
c1[r, 15].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 16].PutValue(R.Text(inWeight));
c1[r, 17].PutValue(R.Text(inVolume));
c1[r, 18].PutValue(R.Text(skuIn));
c1[r, 19].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 20].PutValue(R.Text(""));
c1[r, 21].PutValue(R.Text(""));
c1[r, 22].PutValue(R.Text(""));
c1[r, 23].PutValue(R.Text(""));
c1[r, 24].PutValue(R.Text(""));
c1[r, 25].PutValue(R.Text(""));
c1[r, 26].PutValue(R.Text(handQty));
c1[r, 27].PutValue(R.Text(dt.Rows[i]["PackTypeOrig"]));
c1[r, 28].PutValue(R.Text(handWeight));
c1[r, 29].PutValue(R.Text(handVolume));
c1[r, 30].PutValue(R.Text(skuQty));
c1[r, 31].PutValue(R.Text(dt.Rows[i]["PackUom"]));
c1[r, 32].PutValue(R.Text(dt.Rows[i]["Marking1"]));
c1[r, 33].PutValue(R.Text(dt.Rows[i]["Marking2"]));
c1[r, 34].PutValue(R.Text(dt.Rows[i]["Remark1"]));
for (int j = 0; j <= 32; j++)
{
c1[r, j].SetStyle(style1);
}
}
lastContNo = contNo;
lastBookingNo = bookingNo;
lastHblNo = hblNo;
lastSkuCode = skuCode;
dr = dt.Rows[i];
cnt = i + 1;
c1.SetRowHeight(r, 20);
r++;
}
Aspose.Cells.Style style3 = workbook.Styles[workbook.Styles.Add()];
style2.Font.IsBold = false;
style2.Font.Size = 12;
style2.HorizontalAlignment = TextAlignmentType.Center;
style2.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
style2.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style2.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
for (int j = 0; j <= 32; j++)
{
c1[r, j].SetStyle(style1);
}
#endregion
workbook.Save(pathOpen, FileFormatType.Excel2003);
MemoryStream ms = new MemoryStream();
workbook.Save(ms, FileFormatType.Excel2003);
byte[] bt = ms.GetBuffer();
try
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Length", bt.Length.ToString());
Response.AddHeader("Content-Disposition", "attachment; filename=" + file);
Response.BinaryWrite(bt);
}
catch (Exception exc)
{
throw new Exception(exc.Message + "/" + exc.StackTrace);
}
ms.Dispose();
}
