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