public string savadata_sale_ship(string jsonbill, string jsondetail, string codeid)
{
common cm = new common();
datacontext db = new datacontext();
int status = -1;
if (codeid == "")
{
codeid = cm.getcodeId("order");
}
else
{
status = 1;
}
List <string> strbill = db.orderbill_str(jsonbill, codeid, status);
List <string> strdetail = db.detailorder_str(jsondetail, codeid, status);
List <string> lw = new List <string>();
lw.AddRange(strbill);
lw.AddRange(strdetail);
sql sql = new sql();
sql.Executesql(string.Join(";", lw));
return("");
}
public string findata_ship(string value)
{
sql sql = new sql();
common cm = new common();
string[] table_bill = new string[] { "orderbill", "paymentoption", "note", "money", "tolerancepercent", "tolerancemoney", "usance", "datebeginpayment", "creditduedate" };
string[] table_ship = new string[] { "numbership", "duedate", "departuredate", "dateofarrival", "conDuedate", "shipname", "remark", "nameshipname", "qltbox", "dateclearance", "customsday", "port", "containernumber", "daycertificate" };
string tb_bill = cm.getclumn(table_bill, "billbuy");
string sqlstr = "select " + tb_bill + ",ship.* from billbuy left join ship on billbuy.codeid=ship.codebill where billbuy.codeid='" + value + "'";
var o = sql.getdatatablejoinSQL(sqlstr);
if (o.Count > 1)
{
for (var i = 1; i < o.Count; i++)
{
foreach (var j in table_bill)
{
o[i][j] = "";
}
}
}
string rs = "";
if (o.Count > 0)
{
rs = JsonConvert.SerializeObject(o);
}
return(rs);
}
public string savadatabill_ship(string jsondetail, string codebill, string inforbill)
{
common cm = new common();
datacontext db = new datacontext();
List <string> lw = db.ship_STR(jsondetail, codebill, inforbill);
sql sql = new sql();
sql.Executesql(string.Join(";", lw));
return("");
}
public List <Dictionary <string, object> > objpage8_4(string where)
{
sql sql = new sql();
common cm = new common();
string[] table_bill_buy = new string[] { "ordernumber", "tradingmission", "warehouse" };
string[] Items = new string[] { "itemname", "standard", "quantityincase" };
string[] table_detail_bill_buy = new string[] { "quantityincase" };
string[] cuctomer = new string[] { "name as namectm", "transactionbalance" };
string[] table_bill_sale = new string[] { "orderdate", "warehouse as warehouse_sale" };
string[] table_detail_bill_sale = new string[] { "qltincase" };
string tb_bill_sale = cm.getclumn(table_bill_sale, "orderslips");
string tb_detail_bill_sale = cm.getclumn(table_detail_bill_sale, "detailorder");
string tb_cuctomer = cm.getclumn(cuctomer, "customer");
string tb_bill_buy = cm.getclumn(table_bill_buy, "billbuy");
string tb_Items = cm.getclumn(Items, "ItemList");
string tb_detail_bill_buy = cm.getclumn(table_detail_bill_buy, "detailbillbuy");
string str = "select " + tb_bill_buy + "," + table_bill_sale + "," + table_detail_bill_sale + "," + tb_detail_bill_buy + "," + tb_cuctomer + "," + tb_Items + "" +
" from ItemList " +
" left join detailbillbuy on ItemList.id=detailbillbuy.iditem" +
" left join detailorder on ItemList.id=detailorder.itemid" +
" left join customer on billbuy.tradingmission=customer.id" +
" left join ItemList on detailbillbuy.iditem=ItemList.id " +
" left join orderdate on billbuy.warehouse=orderdate.warehouse" +
" left join detailorder on orderdate.codeid=detailorder.codebill" +
" group by " + tb_bill_buy + "," + tb_detail_bill_buy + "," + tb_Items + "," + table_bill_sale + ",customer.name,customer.transactionbalance";
var mainData = sql.getdatatablejoinSQL(str);
//foreach (var i in mainData)
//{
// i["sum_amount_order"] = cm.parse(i["transactionbalance"].ToString()) - (cm.parse(i["cashT_T_sale"].ToString()) + cm.parse(i["billL_C_sale"].ToString()) + cm.parse(i["feesmoney_sale"].ToString()));
// i["sum_amount_buy"] = cm.parse(i["transactionbalance"].ToString()) + (cm.parse(i["cashT_T_buy"].ToString()) + cm.parse(i["billL_C_buy"].ToString()) + cm.parse(i["feesmoney_buy"].ToString()));
// i["Unpaid_order"] = 0;
// i["Unpaid_buy"] = 0;
// i["total_buy"] = cm.parse(i["sum_amount_buy"].ToString()) + cm.parse(i["Unpaid_buy"].ToString());
// i["total_order"] = cm.parse(i["sum_amount_order"].ToString()) + cm.parse(i["Unpaid_order"].ToString());
// i["balance"] = "";
// foreach (var j in moneycl)
// {
// i[j] = cm.convernumbertostring(i[j].ToString());
// }
//}
return(mainData);
}
public List <Dictionary <string, object> > s_obj_2_4(string where)
{
common cm = new common();
var cvdate = cm.cvdate;
var cvmoney = cm.moneycl;
sql sql = new sql();
string[] table_bill = new string[] { "ordernumber", "terms", "contractdate", "datebeginpayment", "notebill", "note", "paymentoption" };
string[] item = new string[] { "itemname", "standard" };
string[] table_detail = new string[] { "qltcontainer", "quantityincase", "qltcase", "qltcase", "vat", "totalamount", "sqm", "price", "pricesupply", "vat", "totalamount" };
string[] warehouse = new string[] { "name as housename" };
string[] employer = new string[] { "name as namewk" };
string[] cuctomer = new string[] { "name as namectm", "transactionbalance", "transactionaccountnumber" };
string[] ship = new string[] { "dateclearance", "departuredate", "customsday", "confirmreceiptday", "numbership", "sizefit", "containerday", "conreturnday", "requestday", "confirmreceiptday", "remark", "duedate", "dateofarrival", "conDuedate", "port", "nameshipname", "daycertificate", "sizefit" };
string tb_bill = cm.getclumn(table_bill, "billbuy");
string tb_item = cm.getclumn(item, "ItemList");
string tb_detail = cm.getclumn(table_detail, "detailbillbuy");
string tb_warehouse = cm.getclumn(warehouse, "warehouse");
string tb_employer = cm.getclumn(employer, "employee");
string tb_cuctomer = cm.getclumn(cuctomer, "customer");
string tb_ship = cm.getclumn(ship, "ship");
string str = "select " + tb_bill + "," + tb_detail + "," + tb_item + "," + tb_warehouse + "," + tb_employer + "," + tb_cuctomer + "," + tb_ship + "" +
" from billbuy left join detailbillbuy on billbuy.codeid=detailbillbuy.codebill " +
" left join warehouse on billbuy.warehouse=warehouse.id " +
" left join employee on billbuy.orderingemployer=employee.id" +
" left join customer on billbuy.tradingmission=customer.id" +
" left join ship on billbuy.codeid=ship.codebill" +
" left join ItemList on detailbillbuy.iditem=ItemList.id " + where +
" group by " + tb_bill + "," + tb_detail + "," + tb_item + ",warehouse.name,employee.name,customer.name,customer.transactionbalance,customer.transactionaccountnumber," + tb_ship + "";
var mainObj = sql.getdatatablejoinSQL(str);
foreach (var i in mainObj)
{
foreach (var j in cvdate)
{
try
{
i[j] = cm.converdate("", i[j].ToString());
}
catch { }
}
}
return(mainObj);
}
public List <Dictionary <string, object> > objpage8_4_buy(string where)
{
sql sql = new sql();
common cm = new common();
string[] table_bill_buy = new string[] { "ordernumber", "tradingmission", "warehouse" };
string[] Items = new string[] { "itemname", "standard", "quantityincase" };
string[] table_detail_bill_buy = new string[] { "quantityincase" };
string[] cuctomer = new string[] { "name as namectm", "transactionbalance" };
string[] table_bill_sale = new string[] { "orderdate" };
string tb_cuctomer = cm.getclumn(cuctomer, "customer");
string tb_bill_buy = cm.getclumn(table_bill_buy, "billbuy");
string tb_Items = cm.getclumn(Items, "ItemList");
string tb_detail_bill_buy = cm.getclumn(table_detail_bill_buy, "detailbillbuy");
string str = "select " + tb_bill_buy + "," + tb_detail_bill_buy + "," + tb_cuctomer + "," + tb_Items + "" +
" from billbuy " +
" left join customer on billbuy.tradingmission=customer.id" +
" left join detailbillbuy on billbuy.codeid=detailbillbuy.codebill" +
" left join ItemList on detailbillbuy.iditem=ItemList.id " +
" group by " + tb_bill_buy + "," + tb_detail_bill_buy + "," + tb_Items + ",customer.name,customer.transactionbalance";
var mainData = sql.getdatatablejoinSQL(str);
double bginvtr1 = 0;
double bginvtr2 = 0;
foreach (var i in mainData)
{
var quantityincase1 = i["quantityincase"].ToString();
if (i["warehouse"].ToString() == "1")
{
bginvtr1 = bginvtr1 + cm.parse(i["quantityincase"].ToString());
}
else
{
bginvtr2 = bginvtr2 + cm.parse(i["quantityincase"].ToString());
}
i["inventory1"] = bginvtr1 - 0;
i["inventory2"] = bginvtr2 - 0;
}
return(mainData);
}
public List <Dictionary <string, object> > objpage8_5(string where)
{
sql sql = new sql();
common cm = new common();
string[] table_bill = new string[] { "salesrepresentative", "customercompany" };
string[] employer = new string[] { "name as namewk" };
string[] cuctomer = new string[] { "name as namectm", "transactionbalance" };
string[] paymentbuy = new string[] { "sum(payment.cashT_T) as cashT_T_buy", "sum(payment.billL_C) as billL_C_buy", "sum(payment.feesmoney) as feesmoney_buy" };
string[] paymentsale = new string[] { "sum(paymentOrder.cashT_T) as cashT_T_sale", "sum(paymentOrder.billL_C) as billL_C_sale", "sum(paymentOrder.feesmoney) as feesmoney_sale" };
string tb_cuctomer = cm.getclumn(cuctomer, "customer");
string tb_employer = cm.getclumn(employer, "employee");
string tb_paymentsale = string.Join(",", paymentsale);
string tb_paymentbuy = string.Join(",", paymentbuy);
string str = "select " + tb_cuctomer + "," + tb_employer + "," + tb_paymentsale + "," + tb_paymentbuy + "" +
" from customer " +
" left join employee on customer.salesemployee=employee.id" +
" left join paymentOrder on customer.id=paymentOrder.customer" +
" left join payment on customer.id=payment.customer where customer.isdelete is null" +
" group by customer.name,customer.transactionbalance,employee.name";
var mainData = sql.getdatatablejoinSQL(str);
foreach (var i in mainData)
{
i["sum_amount_order"] = cm.parse(i["transactionbalance"].ToString()) - (cm.parse(i["cashT_T_sale"].ToString()) + cm.parse(i["billL_C_sale"].ToString()) + cm.parse(i["feesmoney_sale"].ToString()));
i["sum_amount_buy"] = cm.parse(i["transactionbalance"].ToString()) + (cm.parse(i["cashT_T_buy"].ToString()) + cm.parse(i["billL_C_buy"].ToString()) + cm.parse(i["feesmoney_buy"].ToString()));
i["Unpaid_order"] = 0;
i["Unpaid_buy"] = 0;
i["total_buy"] = cm.parse(i["sum_amount_buy"].ToString()) + cm.parse(i["Unpaid_buy"].ToString());
i["total_order"] = cm.parse(i["sum_amount_order"].ToString()) + cm.parse(i["Unpaid_order"].ToString());
i["balance"] = "";
foreach (var j in moneycl)
{
i[j] = cm.convernumbertostring(i[j].ToString());
}
}
return(mainData);
}
public List <Dictionary <string, object> > objTable_Erorr(string where)
{
sql sql = new sql();
common cm = new common();
string[] table_erorr = new string[] { "numbercode", "poor_handling_charge", "warehouse_id", "defective_processing_type", "approved", "remark" };
string[] item = new string[] { "itemcode", "itemname", "standard" };
string[] table_detail = new string[] { "qltincase", "qltbox", "qltremove", "serinum", "Defect_type", "remark as remark1", "remarkremove" };
string[] warehouse = new string[] { "name as housename" };
string[] employer = new string[] { "name as namewk" };
string table = getclumn(table_erorr, "Table_Erorr");
string itemstr = getclumn(item, "ItemList");
string employerstr = getclumn(employer, "employee");
string warehousestr = getclumn(warehouse, "warehouse");
string tb_detail = getclumn(table_detail, "table_detail_erorr");
string str = "select " + table + "," + tb_detail + "," + itemstr + "," + warehousestr + "," + employerstr + "" +
" from Table_Erorr left join table_detail_erorr on Table_Erorr.codeid=table_detail_erorr.codebill " +
" left join ItemList on table_detail_erorr.Item_id=ItemList.id" +
" left join warehouse on Table_Erorr.warehouse_id=warehouse.id" +
" left join employee on Table_Erorr.poor_handling_charge=employee.id";
var mainData = sql.getdatatablejoinSQL(str);
foreach (var i in mainData)
{
i["approved"] = cm.converdate("yy.MM.dd", i["approved"].ToString());
string itemcodea = itemcode(i["itemcode"].ToString(), 4);
if (itemcodea != "0000")
{
itemcodea = itemcodea + "-01";
}
else
{
itemcodea = "";
}
i["itemcodenew"] = itemcodea;
}
return(mainData);
}
