public string exceldata(string[] title, string[] query, List <string> clnumber, string name, string tabledt)
{
sql sql = new sql();
System.Data.DataTable table = new System.Data.DataTable();
foreach (var i in title)
{
if (clnumber.IndexOf(i) != -1)
{
table.Columns.Add(i, typeof(double));
}
else
{
table.Columns.Add(i, typeof(string));
}
}
var data = sql.getdatatableSQL(tabledt, "where isdelete is null");
int order = 1;
foreach (var i in data)
{
List <string> r = new List <string>();
foreach (var j in query)
{
try
{
r.Add(i[j].ToString());
}
catch
{
r.Add(order.ToString());
}
}
table.Rows.Add(
r.ToArray()
);
order++;
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(table, "sheet1");
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + name + ".xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
}
return("ok");
}
public List <Dictionary <string, object> > objOrderpayment(string where, List <Dictionary <string, object> > employer, List <Dictionary <string, object> > customer, List <Dictionary <string, object> > litem)
{
sql sql = new sql();
string orderslips = "orderslips.*";
string[] detailorder = new string[] { "itemname", "standard", "qltincase", "qltcase", "price", "pricesupply", "vat", "totalamount" };
string dtail = getclumn(detailorder, "detailorder");
string str = "select " + orderslips + ",paymentOrder.*," + dtail + ",carship.*,customer.transactionbalance from orderslips " +
" left join paymentOrder on orderslips.codeid=paymentOrder.codebill" +
" left join detailorder on orderslips.codeid=detailorder.codebill" +
" left join customer on orderslips.customercompany=customer.id" +
" left join carship on orderslips.codeid=carship.codebill";
var mainData = new List <Dictionary <string, object> >();
var mm = sql.getdatatablejoinSQL(str);
var a = sql.getdatatableSQL("orderslips", where);
foreach (var i in a)
{
Dictionary <string, object> mdata = new Dictionary <string, object>();
var o = mm.FirstOrDefault(s => s["orderdate"].ToString() == i["orderdate"].ToString());
o["itemname"] = getdatainlist(litem, o["itemname"].ToString(), "itemname");
o["salesrepresentative"] = getdatainlist(employer, o["salesrepresentative"].ToString(), "name");
o["customercompany"] = getdatainlist(customer, o["customercompany"].ToString(), "name");
foreach (var j in cvdate)
{
try
{
o[j] = converdate("yy.MM.dd", o[j].ToString());
}
catch
{
}
}
mdata["sumofpayments"] = parse(o["cashT_T"].ToString()) + parse(o["billL_C"].ToString()) + parse(o["feesmoney"].ToString());
mdata["moneysale"] = mm.Where(s => s["orderdate"].ToString() == i["orderdate"].ToString()).Sum(s => parse(s["totalamount"].ToString()));
string aa = mdata["moneysale"].ToString();
var bb = o["transactionbalancectm"].ToString();
mdata["summoney"] = parse(aa) + parse(bb);
mdata["total"] = parse(mdata["summoney"].ToString()) - parse(mdata["sumofpayments"].ToString());
foreach (var j in clgetdata)
{
try
{
mdata[j] = o[j].ToString();
}
catch { }
if (moneycl.IndexOf(j) != -1)
{
mdata[j] = String.Format("{0:0,0}", mdata[j]);
}
}
mainData.Add(mdata);
}
return(mainData);
}
public List <Dictionary <string, object> > objpayment(string where, List <Dictionary <string, object> > employer, List <Dictionary <string, object> > customer, List <Dictionary <string, object> > litem)
{
sql sql = new sql();
string str = "select billbuy.*,payment.*,detailbillbuy.*,ship.*,customer.transactionbalance from billbuy " +
" left join detailbillbuy on billbuy.codeid=detailbillbuy.codebill" +
" left join payment on billbuy.codeid=payment.codebill" +
" left join customer on billbuy.tradingmission=customer.id" +
" left join ship on billbuy.codeid=ship.codebill " + where;
var mm = sql.getdatatablejoinSQL(str);
var a = sql.getdatatableSQL("billbuy", where);
List <Dictionary <string, object> > mainData = new List <Dictionary <string, object> >();
foreach (var i in a)
{
Dictionary <string, object> mdata = new Dictionary <string, object>();
var o = mm.FirstOrDefault(s => s["ordernumber"].ToString() == i["ordernumber"].ToString());
o["nameitem"] = getdatainlist(litem, o["nameitem"].ToString(), "itemname");
foreach (var j in cvdate)
{
try
{
o[j] = converdate("yy.MM.dd", o[j].ToString());
}
catch
{
}
}
foreach (var j in clgetdata)
{
try
{
mdata[j] = o[j].ToString();
}
catch { }
if (moneycl.IndexOf(j) > -1)
{
try
{
mdata[j] = String.Format("{0:0,0}", o[j]);
}
catch { }
}
}
mdata["sumofpayments"] = parse(o["cashT_T"].ToString()) + parse(o["billL_C"].ToString()) + parse(o["feesmoney"].ToString());
mdata["total"] = parse(o["transactionbalance"].ToString()) - parse(mdata["sumofpayments"].ToString());
mainData.Add(mdata);
}
return(mainData);
}
public string getdatanosort(string obj, string column, string value)
{
sql sql = new sql();
var o = sql.getdatatableSQL(obj, "where " + column + "='" + value + "'");
string rs = "";
if (o != null && o.Count > 0)
{
rs = JsonConvert.SerializeObject(o);
}
return(rs);
}
public List <Dictionary <string, object> > objplans(string where, List <Dictionary <string, object> > employer)
{
sql sql = new sql();
var mainData = sql.getdatatableSQL("plans", "order by id desc");
foreach (var i in mainData)
{
i["worker"] = getdatainlist(employer, i["worker"].ToString(), "name");
i["time"] = converdate("yyyy.MM", i["time"].ToString());
i["money"] = String.Format("{0:0,0}", i["money"]);
}
return(mainData);
}
