Esempio n. 1
0
        public List <Dictionary <string, object> > objOrder(string where, List <Dictionary <string, object> > employer, List <Dictionary <string, object> > customer, List <Dictionary <string, object> > litem)
        {
            sql    sql         = new sql();
            string strcar      = stringCarship();
            string stringorded = stringordedt();
            string bill        = "select " + strcar + ",orderslips.*," + stringorded + " from orderslips left join detailorder on orderslips.codeid=detailorder.codebill left join carship on orderslips.codeid=carship.codebill " + where + " order by id desc";
            var    mainData    = sql.getdatatablejoinSQL(bill);

            foreach (var i in mainData)
            {
                i["company"]             = getdatainlist(customer, i["company"].ToString(), "name");
                i["deliveryworker"]      = getdatainlist(employer, i["deliveryworker"].ToString(), "name");
                i["salesrepresentative"] = getdatainlist(employer, i["salesrepresentative"].ToString(), "name");
                i["itemname"]            = getdatainlist(litem, i["itemname"].ToString(), "itemname");
                i["codeid"]          = getdatainlist(customer, i["customercompany"].ToString(), "ctmzone");
                i["customercompany"] = getdatainlist(customer, i["customercompany"].ToString(), "name");
                i["issuedate"]       = converdate("yy.MM.dd", i["issuedate"].ToString());
                i["itemcode"]        = getdatainlist(litem, i["itemname"].ToString(), "itemduzoncode");
                i["shippingtime"]    = converdate("yy.MM.dd H:mm", i["shippingtime"].ToString());
                i["rate"]            = String.Format("{0:0,0}", i["rate"]);
                i["price"]           = String.Format("{0:0,0}", i["price"]);
                i["pricesupply"]     = String.Format("{0:0,0}", i["pricesupply"]);
                i["vat"]             = String.Format("{0:0,0}", i["vat"]);
                i["totalamount"]     = String.Format("{0:0,0}", i["totalamount"]);
                if (i["verifyingacceptance"].ToString().ToLower() == "true")
                {
                    i["verifyingacceptance"] = "OK";
                }
                else
                {
                    i["verifyingacceptance"] = "";
                }
            }
            return(mainData);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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("");
        }
Esempio n. 4
0
        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");
        }
Esempio n. 5
0
        public string savehistory(string codeid, string oldstr, string data, string act, string subcode, string inforbill)
        {
            sql sql = new sql();

            string[] htr      = new string[] { "codeid", "contenold", "contennew", "actions", "subcode", "inforbill" };
            string[] vlhtr    = new string[] { codeid, oldstr, data, act, subcode, inforbill };
            string   cretehtr = sql.insertstr("historybillsale", htr.ToList(), vlhtr.ToList());

            return(cretehtr);
        }
Esempio n. 6
0
        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("");
        }
Esempio n. 7
0
        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);
        }
Esempio n. 8
0
        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);
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        public string findata(string obj, string column, string value)
        {
            sql    sql = new sql();
            var    o   = sql.finddatasql(obj, "where " + column + "='" + value + "'");
            string rs  = "";

            if (o.Count > 0)
            {
                rs = JsonConvert.SerializeObject(o);
            }
            return(rs);
        }
Esempio n. 11
0
        public List <Dictionary <string, object> > s_objpayment(string where)
        {
            sql sql = new sql();

            string[] table_bill   = new string[] { "ordernumber", "datebeginpayment", "paymentoption", "creditduedate", "tolerancemoney", "usance", "tolerancepercent", "note", "orderingemployer", "tradingmission", "namewarehouse", "terms", "remark", "notebill" };
            string[] item         = new string[] { "itemname", "standard" };
            string[] table_detail = new string[] { "quantityincase", "qltcase", "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" };
            string[] ship         = new string[] { "port", "customsday", "numbership", "duedate", "departuredate", "dateofarrival" };
            string[] payment      = new string[] { "cashT_T", "billL_C", "feesmoney", "remark" };
            string   table        = getclumn(table_bill, "billbuy");
            string   itemstr      = getclumn(item, "ItemList");
            string   employerstr  = getclumn(employer, "employee");
            string   warehousestr = getclumn(warehouse, "warehouse");
            string   tb_detail    = getclumn(table_detail, "detailbillbuy");
            string   tb_cuctomer  = getclumn(cuctomer, "customer");
            string   tb_ship      = getclumn(ship, "ship");
            string   tb_payment   = getclumn(payment, "payment");
            string   str          = "select " + table + "," + tb_detail + "," + itemstr + "," + employerstr + "," + warehousestr + "," + tb_cuctomer + "," + tb_ship + "," + tb_payment + "" +
                                    " from billbuy " +
                                    " left join detailbillbuy on billbuy.codeid=detailbillbuy.codebill" +
                                    " left join ItemList on detailbillbuy.iditem=ItemList.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 payment on billbuy.codeid=payment.codebill" +
                                    " left join warehouse on billbuy.warehouse=warehouse.id " + where +
                                    " group by " + table + ",ItemList.itemname,ItemList.standard," + tb_detail + ",employee.name,warehouse.name,customer.name,customer.transactionbalance," + tb_ship + "," + tb_payment + "";

            var mainData = sql.getdatatablejoinSQL(str);

            foreach (var i in mainData)
            {
                double sumofpayments = parse(i["cashT_T"].ToString()) + parse(i["billL_C"].ToString()) + parse(i["feesmoney"].ToString());
                i["sumofpayments"] = sumofpayments;
                i["total"]         = sumofpayments + parse(i["transactionbalance"].ToString());
                foreach (var j in clgetdata)
                {
                    if (cvdate.Contains(j))
                    {
                        i[j] = converdate("yy.MM.dd", i[j].ToString());
                    }
                    if (moneycl.Contains(j))
                    {
                        i[j] = convernumbertostring(i[j].ToString());
                    }
                }
            }
            return(mainData);
        }
Esempio n. 12
0
        public string getdatadetailbillbuy(string value)
        {
            sql    sql = new sql();
            string str = "select ItemList.itemname,ItemList.itemcode,ItemList.standard,detailbillbuy.* from detailbillbuy left join ItemList on detailbillbuy.iditem=ItemList.id where detailbillbuy.codebill='" + value + "'";
            var    o   = sql.getdatatablejoinSQL(str);
            string rs  = "";

            if (o != null && o.Count > 0)
            {
                rs = JsonConvert.SerializeObject(o);
            }
            return(rs);
        }
Esempio n. 13
0
        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);
        }
Esempio n. 14
0
        public string getdataship(string obj, string column, string value)
        {
            sql    sql    = new sql();
            string sqlstr = "select ship.*,subship.* from ship inner join subship on ship.idbill==subship.idbill where ship.idbill=" + value;
            var    o      = sql.getdatatablejoinSQL(sqlstr);
            string rs     = "";

            if (o.Count > 0)
            {
                rs = JsonConvert.SerializeObject(o);
            }
            return(rs);
        }
Esempio n. 15
0
        public string countdata(string table, string code, string vl)
        {
            sql sql = new sql();

            string where = "";
            if (code != "")
            {
                where = "where " + code + "=N'" + vl + "'";
            }
            string count = sql.countitem(table, where);

            return(count);
        }
Esempio n. 16
0
        public string historybill(string value)
        {
            sql    sql = new sql();
            var    str = "select historybillsale.contenold,historybillsale.subcode,historybillsale.inforbill from historybillsale where codeid='" + value + "' AND actions='edit detail buy'";
            var    o   = sql.getdatatablejoinSQL(str);
            string rs  = "";

            if (o.Count > 0)
            {
                rs = JsonConvert.SerializeObject(o);
            }
            return(rs);
        }
Esempio n. 17
0
        public string findidobj2(string table, string columncp, string vl, string clget)
        {
            sql sql = new sql();

            try
            {
                return(sql.finddatasql(table, "where " + columncp)[clget].ToString());
            }
            catch
            {
                return("-1");
            }
        }
Esempio n. 18
0
        public string convertId(string table, string column, string vl)
        {
            sql cmd = new sql();
            var elm = cmd.getcolumndatatableSQL(table, "id", "where " + column + "=N'" + vl + "'");

            if (elm.Count > 0)
            {
                return(elm[0]["id"].ToString());
            }
            else
            {
                return("");
            }
        }
Esempio n. 19
0
        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);
        }
Esempio n. 20
0
        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);
        }
Esempio n. 21
0
        public List <Dictionary <string, object> > s_objOrder(string where)
        {
            sql sql = new sql();

            string[] item         = new string[] { "itemname", "standard" };
            string[] table_detail = new string[] { "qltincase", "qltcase", "price", "pricesupply", "vat", "totalamount" };
            string[] employer     = new string[] { "name as namewk" };
            string[] carship      = new string[] { "shippingtime" };
            string[] cuctomer     = new string[] { "name as namectm" };
            string[] orderslips   = new string[] { "id as idbill", "salesrepresentative", "customercompany", "warehouse" };
            string   tb_cuctomer  = getclumn(cuctomer, "customer");
            string   table        = getclumn(orderslips, "orderslips");
            string   tb_item      = getclumn(item, "ItemList");
            string   tb_detail    = getclumn(table_detail, "detailorder");
            string   tb_car       = getclumn(carship, "carship");
            string   tb_employer  = getclumn(employer, "employee");
            string   bill         = "select " + tb_item + "," + table + "," + tb_detail + "," + tb_detail + "," + tb_car + "," + tb_employer + "," + tb_cuctomer + ",warehouse.subname as store" +
                                    " from orderslips left join detailorder on orderslips.codeid=detailorder.codebill" +
                                    " left join carship on orderslips.codeid=carship.codebill " +
                                    " left join employee on orderslips.salesrepresentative=employee.id " +
                                    " left join customer on orderslips.customercompany=customer.id " +
                                    " left join warehouse on orderslips.warehouse=warehouse.id " +
                                    " left join ItemList on detailorder.itemid=ItemList.id " + where + " order by orderslips.id desc";
            var mainData = sql.getdatatablejoinSQL(bill);

            foreach (var i in mainData)
            {
                foreach (var j in clgetdata)
                {
                    if (cvdate.Contains(j))
                    {
                        i[j] = converdate("yy.MM.dd", i[j].ToString());
                    }
                    if (moneycl.Contains(j))
                    {
                        i[j] = convernumbertostring(i[j].ToString());
                    }
                }
                i["shippingtime"] = converdate("yy.MM.dd H:mm", i["shippingtime"].ToString());
            }
            return(mainData);
        }
Esempio n. 22
0
        public List <Dictionary <string, object> > s_objpayment2_9(string where)
        {
            sql sql = new sql();

            string[] table_bill  = new string[] { "ordernumber", "orderingemployer", "tradingmission" };
            string[] employer    = new string[] { "name as namewk" };
            string[] cuctomer    = new string[] { "name as namectm", "transactionbalance" };
            string[] payment     = new string[] { "cashT_T", "billL_C", "feesmoney", "remark", "paymentdate", "paymentaccount" };
            string   table       = getclumn(table_bill, "billbuy");
            string   employerstr = getclumn(employer, "employee");

            string tb_cuctomer = getclumn(cuctomer, "customer");

            string tb_payment = getclumn(payment, "payment");
            string str        = "select " + table + "," + employerstr + "," + tb_cuctomer + "," + tb_payment + "" +
                                " from billbuy " +
                                " left join detailbillbuy on billbuy.codeid=detailbillbuy.codebill" +
                                " left join employee on billbuy.orderingemployer=employee.id" +
                                " left join customer on billbuy.tradingmission=customer.id" +
                                " left join payment on billbuy.codeid=payment.codebill " + where +
                                " group by " + table + "," + tb_payment + ",employee.name,customer.name,customer.transactionbalance";
            var mainData = sql.getdatatablejoinSQL(str);

            foreach (var i in mainData)
            {
                double sumofpayments = parse(i["cashT_T"].ToString()) + parse(i["billL_C"].ToString()) + parse(i["feesmoney"].ToString());
                i["sumofpayments"] = sumofpayments;
                i["total"]         = sumofpayments + parse(i["transactionbalance"].ToString());
                foreach (var j in clgetdata)
                {
                    if (cvdate.Contains(j))
                    {
                        i[j] = converdate("yy.MM.dd", i[j].ToString());
                    }
                    if (moneycl.Contains(j))
                    {
                        i[j] = convernumbertostring(i[j].ToString());
                    }
                }
            }
            return(mainData);
        }
Esempio n. 23
0
        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);
        }
Esempio n. 24
0
        public List <Dictionary <string, object> > s_paymentfrom3_1(string where)
        {
            sql sql = new sql();

            string[] table_bill = new string[] { "salesrepresentative", "customercompany" };
            string[] employer   = new string[] { "name as namewk" };
            string[] cuctomer   = new string[] { "name as namectm", "transactionbalance" };
            string[] payment    = new string[] { "cashT_T", "billL_C", "feesmoney", "remark", "paymentdate", "paymentaccount" };

            string tb_cuctomer = getclumn(cuctomer, "customer");
            string table       = getclumn(table_bill, "orderslips");
            string tb_employer = getclumn(employer, "employee");
            string tb_payment  = getclumn(payment, "paymentOrder");
            string bill        = "select " + table + "," + tb_employer + "," + tb_cuctomer + "," + tb_payment + "" +
                                 " from orderslips " +
                                 " left join employee on orderslips.salesrepresentative=employee.id " +
                                 " left join customer on orderslips.customercompany=customer.id " +
                                 " left join paymentOrder on orderslips.codeid=paymentOrder.codebill " + where + " order by orderslips.id desc";
            var mainData = sql.getdatatablejoinSQL(bill);

            foreach (var i in mainData)
            {
                double sumofpayments = parse(i["cashT_T"].ToString()) + parse(i["billL_C"].ToString()) + parse(i["feesmoney"].ToString());
                i["sumofpayments"] = sumofpayments;
                i["total"]         = sumofpayments + parse(i["transactionbalance"].ToString());

                foreach (var j in clgetdata)
                {
                    if (cvdate.Contains(j))
                    {
                        i[j] = converdate("yy.MM.dd", i[j].ToString());
                    }
                    if (moneycl.Contains(j))
                    {
                        i[j] = convernumbertostring(i[j].ToString());
                    }
                }
            }
            return(mainData);
        }
Esempio n. 25
0
        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);
        }
Esempio n. 26
0
        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);
        }
Esempio n. 27
0
        public List <Dictionary <string, object> > s_obj_buy_in_contry(string where)
        {
            sql sql = new sql();

            string[] table_bill   = new string[] { "ordernumber", "orderingemployer", "tradingmission", "namewarehouse", "terms", "remark" };
            string[] item         = new string[] { "itemname", "standard" };
            string[] table_detail = new string[] { "quantityincase", "qltcase", "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" };
            string   table        = getclumn(table_bill, "billbuy");
            string   itemstr      = getclumn(item, "ItemList");
            string   employerstr  = getclumn(employer, "employee");
            string   warehousestr = getclumn(warehouse, "warehouse");
            string   tb_detail    = getclumn(table_detail, "detailbillbuy");
            string   tb_cuctomer  = getclumn(cuctomer, "customer");
            string   str          = "select " + table + "," + tb_detail + "," + itemstr + "," + employerstr + "," + warehousestr + "," + tb_cuctomer + " from billbuy " +
                                    " left join detailbillbuy on billbuy.codeid=detailbillbuy.codebill" +
                                    " left join ItemList on detailbillbuy.iditem=ItemList.id" +
                                    " left join employee on billbuy.orderingemployer=employee.id" +
                                    " left join customer on billbuy.tradingmission=customer.id" +
                                    " left join warehouse on billbuy.warehouse=warehouse.id " + where;

            var mainData = sql.getdatatablejoinSQL(str);

            foreach (var i in mainData)
            {
                foreach (var j in clgetdata)
                {
                    if (moneycl.IndexOf(j) > -1)
                    {
                        i[j] = convernumbertostring(i[j].ToString());
                    }
                }
            }
            return(mainData);
        }