private Dictionary <String, DataTable> getPrintableData(Dictionary <String, Object> param)
{
Dictionary <String, DataTable> result = new Dictionary <String, DataTable>();
System.Data.DataTable tbl = new System.Data.DataTable();
tbl = new System.Data.DataTable();
tbl.Columns.Add(new DataColumn("TripNo", typeof(string)));
tbl.Columns.Add(new DataColumn("SDRRDate", typeof(DateTime)));
tbl.Columns.Add(new DataColumn("SDRRNo", typeof(string)));
tbl.Columns.Add(new DataColumn("Client", typeof(string)));
tbl.Columns.Add(new DataColumn("Route", typeof(string)));
tbl.Columns.Add(new DataColumn("Zone", typeof(string)));
tbl.Columns.Add(new DataColumn("Cartons", typeof(string)));
tbl.Columns.Add(new DataColumn("Amount", typeof(Decimal)));
DataRow tblRow;
//HeaderName = "List of All SDRR for " + param["client"];
String SQLCommand;
if (param["client"].ToString() == "ALL CLIENTS")
{
SQLCommand = "SELECT SM.[DATE],PT.[SDRRNO],SM.[CLIENT],SM.[ROUTE],MAX(ZONE) [zone],SUM(CONVERT(DECIMAL(18,2),NO_OF_CARTONS))[cartons],SUM(PT.invoice_value)[amount] FROM GLOBAL_SDRRMASTER SM JOIN GLOBAL_PRODUCTTRANS PT ON SM.SDRRNO = PT.SDRRNO WHERE SM.[Date] BETWEEN '" + param["datefrom"] + "' AND '" + param["dateto"] + "' GROUP BY SM.[DATE],PT.[SDRRNO],SM.[CLIENT],SM.[ROUTE]";
}
else
{
SQLCommand = "SELECT SM.[DATE],PT.[SDRRNO],SM.[CLIENT],SM.[ROUTE],MAX(ZONE) [zone],SUM(CONVERT(DECIMAL(18,2),NO_OF_CARTONS))[cartons],SUM(PT.invoice_value)[amount] FROM GLOBAL_SDRRMASTER SM JOIN GLOBAL_PRODUCTTRANS PT ON SM.SDRRNO = PT.SDRRNO WHERE CLIENT = '" + param["client"] + "' AND SM.[Date] BETWEEN '" + param["datefrom"] + "' AND '" + param["dateto"] + "' GROUP BY SM.[DATE],PT.[SDRRNO],SM.[CLIENT],SM.[ROUTE]";
}
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=dispatch;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
Dictionary <String, DataRow> sdrrtrip = new Dictionary <String, DataRow>();
sdrrtrip = Utils.BuildIndex_SQL("select * FROM GLOBAL_SDRRTrip", "SDRRNo");
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=sdrr;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
foreach (DataRow dRow in Utils.GetDataTable(SQLCommand).Rows)
{
tblRow = tbl.NewRow();
DataRow RowTrip = null;
if (sdrrtrip.TryGetValue(dRow["sdrrno"].ToString().Trim(), out RowTrip))
{
tblRow["TripNo"] = RowTrip["tripindex"].ToString().Trim();
}
tblRow["SDRRDate"] = Convert.ToDateTime(dRow["date"].ToString()).ToShortDateString();
tblRow["SDRRNo"] = dRow["sdrrno"].ToString();
tblRow["Client"] = dRow["Client"].ToString();
tblRow["Route"] = dRow["route"].ToString();
tblRow["Zone"] = dRow["zone"].ToString();
tblRow["Cartons"] = dRow["cartons"].ToString();
tblRow["Amount"] = dRow["amount"].ToString();
tbl.Rows.Add(tblRow);
}
DataView dv = tbl.DefaultView;
dv.Sort = "SDRRDate Asc, TripNo Asc,SDRRNo Asc";
DataTable sortedDT = dv.ToTable();
result.Add("main", sortedDT);
return(result);
}
private void InitializeGrid()
{
String ConnectionString;
lblSDRRNo.Text = _clientname[1];
lblpath.Text = "";
invoicedata.Columns.Add(new DataColumn("gridcolno", typeof(Boolean)));
invoicedata.Columns.Add(new DataColumn("gridcolinvno", typeof(string)));
invoicedata.Columns.Add(new DataColumn("gridcolamt", typeof(string)));
invoicedata.Columns.Add(new DataColumn("Invoice_Date", typeof(string)));
invoicedata.Columns.Add(new DataColumn("gridcolcust", typeof(string)));
invoicedata.Columns.Add(new DataColumn("gridcolzone", typeof(string)));
invoicedata.Columns.Add(new DataColumn("Term", typeof(string)));
invoicedata.Columns.Add(new DataColumn("index", typeof(string)));
if (Utils.ClientConnection.ContainsKey(_clientname[0]))
{
if (Utils.ClientConnection[_clientname[0]]["DBTYPE"] == "DOS")
{
try
{
ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = {0};Extended Properties =dBase IV;", Utils.ClientConnection[_clientname[0]]["Directory"]);
using (OleDbConnection dBaseConnection = new OleDbConnection(ConnectionString))
{
dBaseConnection.Open();
System.Data.OleDb.OleDbCommand dBaseCommand;
dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT TERM, ZONEFLAG, SINUMBER, SIDATE, GROSSAMT, CUSTNAME ORDER BY sidate DESC", dBaseConnection);
System.Data.OleDb.OleDbDataReader dBaseDataReader;
dBaseDataReader = dBaseCommand.ExecuteReader();
while (dBaseDataReader.Read())
{
DataRow tblRow = invoicedata.NewRow();
tblRow["gridcolno"] = false;
tblRow["term"] = dBaseDataReader["term"];
tblRow["gridcolzone"] = dBaseDataReader["zoneflag"];
tblRow["gridcolinvno"] = dBaseDataReader["sinumber"];
tblRow["invoice_date"] = dBaseDataReader["sidate"];
tblRow["gridcolamt"] = dBaseDataReader["grossamt"];
tblRow["gridcolcust"] = dBaseDataReader["custname"];
tblRow["gridcolindex"] = 0;
invoicedata.Rows.Add(tblRow);
}
}
lblpath.Text = Utils.ClientConnection[_clientname[0]]["Directory"];
}
catch (OleDbException ex)
{
DevComponents.DotNetBar.MessageBoxEx.Show(String.Format("{0}:{1}", ex.ErrorCode, ex.Message));
}
}
else if (Utils.ClientConnection[_clientname[0]]["DBTYPE"] == "SQL")
{
DataSupport.setDBConnection(Utils.ClientConnection[_clientname[0]]["Directory"]);
invoicedata = Utils.GetDataTable("SELECT 'false'[gridcolno],R.terms[term],R.zone[gridcolzone],docno[gridcolinvno],R.docdate[invoice_date],grossamt[gridcolamt],c.name[gridcolcust],0[gridcolindex] FROM REPORT_register R join base_customer C ON C._custindex = R.sourcecode WHERE R.DocType = 2 order by convert(datetime, R.docdate, 101) DESC", null);
}
else if (Utils.ClientConnection[_clientname[0]]["DBTYPE"] == "SDF")
{
}
if (_invoice.Count >= 1)
{
foreach (KeyValuePair <String, List <String> > item in _invoice)
{
foreach (DataRow invoice in invoicedata.Select(String.Format("gridcolinvno={0}", item.Value[0])))
{
invoice["gridcolno"] = true;
invoice["gridcolindex"] = item.Value[item.Value.Count - 3];
string s = item.Value[item.Value.Count - 3];
}
}
}
gridinvoice.PrimaryGrid.DataSource = invoicedata;
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=sdrr;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
}
}
private Dictionary <String, DataTable> getPrintableData(Dictionary <String, Object> param)
{
Dictionary <String, DataTable> result = new Dictionary <String, DataTable>();
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add(new DataColumn("TripNo", typeof(string)));
tbl.Columns.Add(new DataColumn("PlateNo", typeof(string)));
tbl.Columns.Add(new DataColumn("TruckType", typeof(string)));
tbl.Columns.Add(new DataColumn("Client", typeof(string)));
tbl.Columns.Add(new DataColumn("SDRRNo", typeof(string)));
tbl.Columns.Add(new DataColumn("Customer", typeof(string)));
tbl.Columns.Add(new DataColumn("Zone", typeof(string)));
tbl.Columns.Add(new DataColumn("isCovered", typeof(string)));
tbl.Columns.Add(new DataColumn("InvoiceNo", typeof(string)));
tbl.Columns.Add(new DataColumn("Amount", typeof(Decimal)));
tbl.Columns.Add(new DataColumn("DateRec", typeof(string)));
tbl.Columns.Add(new DataColumn("Status", typeof(string)));
tbl.Columns.Add(new DataColumn("Remarks", typeof(string)));
tbl.Columns.Add(new DataColumn("invoicedate", typeof(string)));
Dictionary <String, DataRow> sdrrmast = new Dictionary <String, DataRow>();
sdrrmast = Utils.BuildIndex_SQL("SELECT * FROM GLOBAL_SDRRMaster", "sdrrno");
Dictionary <String, DataRow> updatedsdrr = new Dictionary <String, DataRow>();
List <String> col = new List <string>();
col.Add("sdrrno");
col.Add("invoice_no");
updatedsdrr = Utils.BuildIndex_SQL("SELECT * FROM GLOBAL_ProductTrans", col, " ");
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=dispatch;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
Dictionary <String, DataRow> sdrrtrip = new Dictionary <String, DataRow>();
sdrrtrip = Utils.BuildIndex_SQL("select SD.tripindex,SD.SDRRNo, TT.VehlIndex FROM GLOBAL_SDRRTrip sd LEFT JOIN GLOBAL_tRIPtICKET tt ON tt.tripno = sd.tripindex AND TT.yearcycle = sd.yearcycle", "SDRRno");
Dictionary <String, DataRow> vehltrip = new Dictionary <String, DataRow>();
vehltrip = Utils.BuildIndex_SQL("select * FROM BASE_Vehicle", "_VehlIndex");
DataSupport.setDBConnection("Integrated Security=SSPI;Initial Catalog=sdrr;Data Source=.\\SQLEXPRESS; User Id = vgl_data; Password = tqbfjotld;");
DataTable zone_sbs = new DataTable();
zone_sbs.Columns.Add(new DataColumn("zone1", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone2", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone3", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone4", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone5", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone6", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone7", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("zone8", typeof(Decimal)));
zone_sbs.Columns.Add(new DataColumn("totalamt", typeof(Decimal)));
DataRow tblRow;
String SQLCommand = "";
if (param["client"].ToString() == "ALL CLIENTS")
{
if (param["delstat"].ToString() == "DELIVERED")
{
HeaderName = "SUMMARY OF BILLING SUPPORT DELIVERED";
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] = '" + param["delstat"].ToString() + "' AND MONTH(US.[datedel]) = " + param["monthcycle"].ToString() + " AND Year(US.[datedel]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],[datedel],US.[invoice_no] ASC,US.[delstat] DESC";
}
else if (param["delstat"].ToString() == "ALL NON-DELIVERED")
{
HeaderName = "SUMMARY OF BILLING SUPPORT " + param["delstat"].ToString();
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] != 'DELIVERED' AND US.[delstat] != '' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
}
else if (param["delstat"].ToString() == "ALL STATUS")
{
HeaderName = "SUMMARY OF BILLING SUPPORT " + param["delstat"].ToString();
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] != 'DELIVERED' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
}
else
{
HeaderName = "SUMMARY OF BILLING SUPPORT " + param["delstat"].ToString();
SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] = '" + param["delstat"].ToString() + "' AND US.[delstat] != '' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " AND invoice_date IS NOT NULL ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
}
}
else
{
//if (param["delstat"].ToString() == "DELIVERED")
// SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] = '" + param["delstat"].ToString() + "' AND SM.Client = '" + param["client"].ToString() + "' AND MONTH(US.[datedel]) = " + param["monthcycle"].ToString() + " AND Year(US.[datedel]) = " + param["yearcycle"].ToString() + " ORDER BY [zone],[datedel],US.[invoice_no] ASC,US.[delstat] DESC";
//else
//{
// HeaderName = "SUMMARY OF BILLING SUPPORT OTHERS";
// SQLCommand = "SELECT DISTINCT US.*, SM.Client FROM GLOBAL_ProductTrans US JOIN GLOBAL_SDRRMaster SM ON SM.SDRRNO = US.SDRRNO WHERE US.[delstat] != 'DELIVERED' AND US.[delstat] != '' AND SM.Client = '" + param["client"].ToString() + "' AND MONTH(SM.[date]) = " + param["monthcycle"].ToString() + " AND Year(SM.[date]) = " + param["yearcycle"].ToString() + " ORDER BY [zone],US.[datedel],US.[invoice_no] ASC, US.[delstat] DESC";
//}
}
DataTable dtRep = Utils.GetDataTable(SQLCommand);
decimal billedamt = 0;
decimal[] zone = new decimal[8];
foreach (DataRow dRow in dtRep.Rows)
{
DataRow Rowmast = null;
DataRow RowUpdate = null;
DataRow RowTrip = null;
DataRow RowVehl = null;
tblRow = tbl.NewRow();
if (sdrrtrip.TryGetValue(dRow["sdrrno"].ToString().Trim(), out RowTrip))
{
tblRow["TripNo"] = RowTrip["tripindex"].ToString();
if (vehltrip.TryGetValue(RowTrip["VehlIndex"].ToString(), out RowVehl))
{
tblRow["PlateNo"] = RowVehl["TruckType"].ToString();
tblRow["TruckType"] = RowVehl["maker"].ToString();
}
}
if (sdrrmast.TryGetValue(dRow["sdrrno"].ToString().Trim(), out Rowmast))
{
tblRow["Client"] = Rowmast["Client"].ToString();
}
String cols = dRow["sdrrno"].ToString().Trim() + " " + dRow["invoice_no"].ToString().Trim();
if (updatedsdrr.TryGetValue(cols, out RowUpdate))
{
tblRow["SDRRNo"] = RowUpdate["sdrrno"].ToString();
tblRow["Customer"] = RowUpdate["Customer_name"].ToString();
tblRow["Zone"] = RowUpdate["Zone"].ToString();
tblRow["isCovered"] = RowUpdate["isCovered"].ToString();
}
if (dRow["DateDel"].ToString() != "")
{
tblRow["DateRec"] = Convert.ToDateTime(dRow["DateDel"].ToString()).ToShortDateString();
}
tblRow["InvoiceNo"] = dRow["invoice_no"].ToString();;
tblRow["Amount"] = dRow["invoice_value"].ToString();
tblRow["Status"] = dRow["delStat"].ToString();
tblRow["Remarks"] = dRow["remarks"].ToString();
if (dRow["invoice_date"].ToString() != "")
{
tblRow["invoicedate"] = Convert.ToDateTime(dRow["invoice_date"]).ToShortDateString();
}
else
{
tblRow["invoicedate"] = dRow["invoice_date"].ToString();
}
tbl.Rows.Add(tblRow);
billedamt += Convert.ToDecimal(tblRow["Amount"]);
switch (tblRow["Zone"].ToString())
{
case "1":
zone[0] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "2":
zone[1] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "3":
zone[2] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "4":
zone[3] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "5":
zone[4] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "6":
zone[5] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "7":
zone[6] += Convert.ToDecimal(tblRow["Amount"]);
break;
case "8":
zone[7] += Convert.ToDecimal(tblRow["Amount"]);
break;
}
}
//if (_reportname == "GENBILLREPORT")
//{
sbs_total = new Dictionary <String, String>();
sbs_total.Add("totalinvoice", dtRep.Rows.Count.ToString());
sbs_total.Add("billedamt", billedamt.ToString());
tblRow = zone_sbs.NewRow();
tblRow["zone1"] = zone[0];
tblRow["zone2"] = zone[1];
tblRow["zone3"] = zone[2];
tblRow["zone4"] = zone[3];
tblRow["zone5"] = zone[4];
tblRow["zone6"] = zone[5];
tblRow["zone7"] = zone[6];
tblRow["zone8"] = zone[7];
tblRow["totalamt"] = zone[0] + zone[1] + zone[2] + zone[3] + zone[4] + zone[5] + zone[6] + zone[7];
zone_sbs.Rows.Add(tblRow);
result.Add("zone", zone_sbs);
sbs_total.Add("batchcode", "");
//}
result.Add("main", tbl);
return(result);
}