protected void BindMemberList(int index) { string where = getWhere(); int count = 0; SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_Working with(nolock)", "*,needPayMoney=0.00,willPayMoney=0.00,AlreadyPayMoney=0.00,pvolume=0.00,pquantity=0.00,receivedNum=0.00,doingNum=0.00,pcode=''", "work_id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { helper.Params.Clear(); dt.Rows[i]["pquantity"] = helper.ExecScalar("select isnull(sum(quantity),0) from Tb_Working_main where work_id=" + dt.Rows[i]["work_id"]); dt.Rows[i]["receivedNum"] = helper.ExecScalar("select isnull(sum(p_quantity),0) from Tb_storage_product a left join dbo.Tb_storage_main b on a.sm_id=b.sm_id where b.sm_type=" + ((int)StorageType.生产入库) + " and b.sm_status in (1,3) and b.relate_sn='" + dt.Rows[i]["work_sn"] + "'"); dt.Rows[i]["doingNum"] = Convert.ToDouble(dt.Rows[i]["pquantity"]) - Convert.ToDouble(dt.Rows[i]["receivedNum"]); dt.Rows[i]["pcode"] = helper.ExecScalar("select top 1 isnull(pro_code,'') from Tb_template a left join Tb_Working_main b on a.tpl_id=b.tpl_id where b.work_id=" + dt.Rows[i]["work_id"]); // dt.Rows[i]["needPayMoney"] = helper.ExecScalar("select top 1 isnull((a.do_cost+a.other_cost)*b.quantity,0) from Tb_template a left join Tb_Working_main b on a.tpl_id=b.tpl_id where b.work_id=" + dt.Rows[i]["work_id"]); //update a set a.do_cost=b.do_cost,a.other_cost=b.other_cost from dbo.Tb_Working_main a,Tb_template b where a.tpl_id=b.tpl_id dt.Rows[i]["needPayMoney"] = helper.ExecScalar("select top 1 isnull(sum((do_cost+other_cost)*quantity),0) from Tb_Working_main where work_id=" + dt.Rows[i]["work_id"]); dt.Rows[i]["AlreadyPayMoney"] = Warehousing.Business.ProductionHelper.getAlreadyPayMoney(Convert.ToInt32(dt.Rows[i]["work_id"])); dt.Rows[i]["willPayMoney"] = Convert.ToDouble(dt.Rows[i]["needPayMoney"]) - Convert.ToDouble(dt.Rows[i]["AlreadyPayMoney"]); } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductionList.Aspx?page={0}&" + queryStr; }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; SqlHelper conn_2 = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("supplier", "*,PayAlready=0.00,PayWill=0.00,PayAll=0.00", "id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { conn_2.Params.Clear(); //应收款 dt.Rows[i]["PayAll"] = conn_2.ExecScalar("select isnull(sum(a.p_quantity*(case b.sm_type when 5 then 0-a.p_price when 7 then 0-a.p_price else a.p_price end)),0) from Tb_storage_product a left join Tb_storage_main b on a.sm_id=b.sm_id where b.sm_type in (1,2,5,7) and b.sm_status in (1) and b.sm_supplierid=" + dt.Rows[i]["id"]); conn_2.Params.Clear(); dt.Rows[i]["PayAlready"] = conn_2.ExecScalar("select isnull(sum(case b.sm_type when 5 then 0-a.pay_money when 7 then 0-a.pay_money else a.pay_money end),0) from Tb_FinancialFlow a left join Tb_storage_main b on a.sm_id=b.sm_id where a.object_type='Storage' and a.is_cancel=0 and b.sm_type in (1,2,5,7) and b.sm_status in (1) and b.sm_supplierid=" + dt.Rows[i]["id"]); dt.Rows[i]["PayWill"] = Convert.ToDouble(dt.Rows[i]["PayAll"]) - Convert.ToDouble(dt.Rows[i]["PayAlready"]); } if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; sm_tax = Convert.ToDouble(conn.ExecScalar("select sm_tax from Tb_plan_main with(nolock) where sm_id=" + id.ToString())); conn.Params.Clear(); string sql = "select * from Tb_plan_product with(nolock) where " + where + " order by p_name,p_spec"; DataTable dt = conn.ExecDataTable(sql); if (dt.Rows.Count == 0) { norecord = 1; } for (int i = 0; i < dt.Rows.Count; i++) { if (p_id_old.IsNullOrEmpty()) { p_id_old = dt.Rows[i]["p_id"].ToString(); } else { p_id_old = p_id_old + "," + dt.Rows[i]["p_id"].ToString(); } } MemberList.DataShow(dt); }
protected void bindDirectPro(int direct_id) { SqlHelper helper = LocalSqlHelper.WH; string sm_sql = "select * from Tb_storage_main with(nolock) where sm_id=@sm_id and is_direct=0"; helper.Params.Add("sm_id", direct_id); DataTable sm_dt = helper.ExecDataTable(sm_sql); if (sm_dt.Rows.Count > 0) { sm_type.SelectedValue = Convert.ToInt32(StorageType.调货出库).ToString(); from_warehouse_id.SelectedValue = sm_dt.Rows[0]["warehouse_id"].ToString(); to_warehouse_id.Style.Value = "display:inline"; // to_warehouse_id.SelectedValue = sm_dt.Rows[0]["warehouse_id_from"].ToString(); } helper.Params.Clear(); string sql = "SELECT *,shelf_no='' FROM Tb_storage_product with(nolock) where sm_id=@sm_id order by p_id"; helper.Params.Add("sm_id", direct_id); DataTable dt = helper.ExecDataTable(sql); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["shelf_no"] = StorageHelper.getShelfNo(Convert.ToInt32(dt.Rows[i]["pro_id"]), Convert.ToInt32(sm_dt.Rows[0]["warehouse_id"])); } MemberList.DataShow(dt); }
protected void BindProductStockList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("View_Product", "*,'' as supplierName,'' as shortSupplierName", "pro_id desc,stock_id desc", false, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { if (my_warehouse_id == 0) { dt.Rows[i]["supplierName"] = Warehousing.Business.StorageHelper.getSupplierName(Convert.ToInt32(dt.Rows[i]["pro_supplierid"])); dt.Rows[i]["shortSupplierName"] = Convert.ToString(dt.Rows[i]["supplierName"]).Length > 4 ? Convert.ToString(dt.Rows[i]["supplierName"]).Substring(0, 4) + ".." : Convert.ToString(dt.Rows[i]["supplierName"]); } } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductStock.aspx?page={0}&" + queryStr; SqlHelper helper = LocalSqlHelper.WH; DataTable dt_new = helper.ExecDataTable("select isnull(sum(pro_inprice*kc_nums),0) as total_amount_base,isnull(sum(pro_outprice*kc_nums),0) as total_amount,isnull(sum(kc_nums),0) as total_kucun from View_Product where " + where); total_amount = Convert.ToDouble(dt_new.Rows[0]["total_amount"]); total_amount_base = Convert.ToDouble(dt_new.Rows[0]["total_amount_base"]); total_kucun = Convert.ToDouble(dt_new.Rows[0]["total_kucun"]); }
protected void bindTemplatePro(int tpl_id) { SqlHelper helper = LocalSqlHelper.WH; string sm_sql = "select * from dbo.Tb_template with(nolock) where tpl_id=@tpl_id"; helper.Params.Add("tpl_id", tpl_id); DataTable sm_dt = helper.ExecDataTable(sm_sql); if (sm_dt.Rows.Count > 0) { factory_id.SelectedValue = sm_dt.Rows[0]["factory_id"].ToString(); } helper.Params.Clear(); string sql = "SELECT a.id,a.pro_nums,b.* FROM dbo.Tb_template_material a left join Prolist b with(nolock) on a.pro_txm_from=b.pro_txm where a.tpl_id=@tpl_id order by id"; helper.Params.Add("tpl_id", tpl_id); DataTable dt = helper.ExecDataTable(sql); for (int i = 0; i < dt.Rows.Count; i++) { if (p_id_old.IsNullOrEmpty()) { p_id_old = dt.Rows[i]["id"].ToString(); } else { p_id_old = p_id_old + "," + dt.Rows[i]["id"].ToString(); } } MemberList.DataShow(dt); }
protected void BindMemberList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; string table = "(select a.txm,isnull(sum(productcount),0) as pcount,isnull(sum(case when productcount<0 then productcount else 0 end),0) as return_count,pro_name,pro_code,pro_spec,pro_model,a.danjia from Direct_OrderDetail a left join Direct_OrderMain b on a.dingdan=b.dingdan left join Prolist p on a.txm=p.pro_txm where " + where + " group by a.txm,pro_name,pro_code,pro_spec,pro_model,a.danjia) as sales"; if (groupby == "huohao") { table = "(select txm='',isnull(sum(productcount),0) as pcount,isnull(sum(case when productcount<0 then productcount else 0 end),0) as return_count,pro_name,pro_code,pro_spec,pro_model='',a.danjia from Direct_OrderDetail a left join Direct_OrderMain b on a.dingdan=b.dingdan left join Prolist p on a.txm=p.pro_txm where " + where + " group by pro_code,pro_name,pro_spec,a.danjia) as sales"; DD_GroupBy.Text = groupby; } //Response.Write(table); //Response.End(); DataTable dt = conn.TablesPageNew(table, "*", "pcount desc,txm asc", true, AspNetPager1.PageSize, index, "", out count); // DataTable dt = PublicHelper.TablesPage(conn, table, "*", "pcount desc", AspNetPager1.PageSize, index, "", "txm", out count); MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "SellTotal.aspx?page={0}&" + queryStr; string totalSql = "select isnull(sum(productcount),0) as pcount,isnull(sum(case when productcount<0 then productcount else 0 end),0) as rcount,isnull(sum(danjia*productcount),0) as sales from Direct_OrderDetail a left join Direct_OrderMain b on a.dingdan=b.dingdan left join Prolist p on a.txm=p.pro_txm where " + where; helper.Params.Clear(); DataTable tdt = helper.ExecDataTable(totalSql); totalCount = Convert.ToInt32(tdt.Rows[0]["pcount"]); totalSales = Convert.ToDouble(tdt.Rows[0]["sales"]); totalCount_return = Convert.ToInt32(tdt.Rows[0]["rcount"]); }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; DataTable dt = conn.ExecDataTable("select * from Tb_storage_product with(nolock) where " + where + " order by p_name,p_spec"); MemberList.DataShow(dt); }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; DataTable dt = conn.ExecDataTable("select *,p_price*p_quantity as moneyall,p_baseprice*p_quantity as basemoneyall,p_baseprice_tax*p_quantity as basemoneyall_tax from Tb_plan_product with(nolock) where " + where + " order by p_name,p_spec"); MemberList.DataShow(dt); }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; SqlHelper conn_2 = LocalSqlHelper.WH; SqlHelper conn_3 = LocalSqlHelper.WH; double cost = 0; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("supplier", "*,PayAlready=0.00,PayWill=0.00,PayAll=0.00,PayGoods=0.00", "id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { conn_2.Params.Clear(); //应收款 dt.Rows[i]["PayAll"] = conn_2.ExecScalar("select isnull(sum(quantity*(do_cost+other_cost)),0) from Tb_Working_main a left join Tb_Working b on a.work_id=b.work_id where b.work_status in (1,3,4,8) and b.factory_id=" + dt.Rows[i]["id"]); conn_2.Params.Clear(); DataTable dt1 = conn_2.ExecDataTable("select c.pro_code,b.relate_sn,quantity=sum(a.p_quantity) from Tb_storage_product a left join Tb_storage_main b on a.sm_id=b.sm_id left join Prolist c on a.p_txm=c.pro_txm where b.sm_type in (5,9) and b.sm_status in (1) and b.sm_supplierid=" + dt.Rows[i]["id"] + " group by c.pro_code,b.relate_sn"); double PayGoods = 0; string sql3 = string.Empty; for (int j = 0; j < dt1.Rows.Count; j++) { conn_3.Params.Clear(); string relate_sn = Convert.ToString(dt1.Rows[j]["relate_sn"]); if (relate_sn.IsNotNullAndEmpty()) { //生产入库9 sql3 = "select top 1 wm.do_cost+wm.other_cost from Tb_Working_main wm left join Tb_Working w on wm.work_id=w.work_id where w.work_sn='" + dt1.Rows[j]["relate_sn"] + "' and wm.pro_code_new='" + dt1.Rows[j]["pro_code"] + "'"; } else { //退货入库 sql3 = "select top 1 0-(wm.do_cost+wm.other_cost) from Tb_Working_main wm left join Tb_Working w on wm.work_id=w.work_id where w.factory_id=" + dt.Rows[i]["id"] + " and wm.pro_code_new='" + dt1.Rows[j]["pro_code"] + "' order by wm.work_id desc"; } // Response.Write(sql3+"<br>"); try { cost = Convert.ToDouble(conn_3.ExecScalar(sql3)); PayGoods += cost * Convert.ToDouble(dt1.Rows[j]["quantity"]); } catch { Response.End(); } } dt.Rows[i]["PayGoods"] = PayGoods; // conn_2.Params.Clear(); dt.Rows[i]["PayAlready"] = conn_2.ExecScalar("select isnull(sum(a.pay_money),0) from Tb_FinancialFlow a left join Tb_Working b on a.sm_id=b.work_id where a.object_type='process' and a.is_cancel=0 and b.work_status in (1,4,3,8) and b.factory_id=" + dt.Rows[i]["id"]); dt.Rows[i]["PayWill"] = Convert.ToDouble(dt.Rows[i]["PayGoods"]) - Convert.ToDouble(dt.Rows[i]["PayAlready"]); } if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; }
protected void BindMemberList() { SqlHelper conn = LocalSqlHelper.WH; DataTable dt = conn.ExecDataTable("select * from SinoModule order by OrderNum desc,ID desc"); if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; DataTable dt = conn.ExecDataTable("select * from Tb_need_product with(nolock) where " + where + " order by p_name,p_spec"); MemberList.DataShow(dt); for (int i = 0; i < dt.Rows.Count; i++) { total_quantity += Convert.ToInt32(dt.Rows[i]["p_quantity"]); total_price += Convert.ToInt32(dt.Rows[i]["p_quantity"]) * Convert.ToDouble(dt.Rows[i]["p_price"]); } }
protected void BindMemberList(int index) { int count = 0; string where = getWhere(); // Response.Write(where); SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; string table = "(select a.p_txm as txm,sum(a.p_quantity) as pcount,pro_name='',pro_code='',pro_spec='',pro_model='',supplierName='',shortSupplierName='',pro_outprice=0.00,pro_inprice=0.00 from Tb_storage_product a left join Tb_storage_main b on a.sm_id=b.sm_id where " + where + " group by a.p_txm) as sales"; //Response.Write(table); DataTable dt = conn.TablesPageNew(table, "*", "txm asc,pcount desc", true, AspNetPager1.PageSize, index, "", out count); // DataTable dt = PublicHelper.TablesPage(conn, table, "*", "pcount desc", AspNetPager1.PageSize, index, "", "txm", out count); for (int i = 0; i < dt.Rows.Count; i++) { conn.Params.Clear(); string sql = "select * from Prolist where pro_txm='" + Convert.ToString(dt.Rows[i]["txm"]) + "'"; // Response.Write(sql); DataTable dtPro = helper.ExecDataTable(sql); if (dtPro.Rows.Count > 0) { dt.Rows[i]["pro_name"] = Convert.ToString(dtPro.Rows[0]["pro_name"]); dt.Rows[i]["pro_code"] = Convert.ToString(dtPro.Rows[0]["pro_code"]); dt.Rows[i]["pro_spec"] = Convert.ToString(dtPro.Rows[0]["pro_spec"]); dt.Rows[i]["pro_model"] = Convert.ToString(dtPro.Rows[0]["pro_model"]); dt.Rows[i]["pro_outprice"] = Convert.ToDouble(dtPro.Rows[0]["pro_outprice"]); dt.Rows[i]["pro_inprice"] = Convert.ToDouble(dtPro.Rows[0]["pro_inprice"]); if (myStorageInfo.agent_id == 0) { dt.Rows[i]["supplierName"] = StorageHelper.getSupplierName(Convert.ToInt32(dtPro.Rows[0]["pro_supplierid"])); dt.Rows[i]["shortSupplierName"] = Convert.ToString(dt.Rows[i]["supplierName"]).Length > 4 ? Convert.ToString(dt.Rows[i]["supplierName"]).Substring(0, 4) + ".." : Convert.ToString(dt.Rows[i]["supplierName"]); } } } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductActive.aspx?page={0}&" + queryStr; string totalSql = "select isnull(sum(p_quantity),0) as pcount,isnull(sum(case when p_quantity<0 then p_quantity else 0 end),0) as rcount,isnull(sum(c.pro_outprice*a.p_quantity),0) as sales,isnull(sum(c.pro_inprice*a.p_quantity),0) as sales_in from Tb_storage_product a left join Tb_storage_main b on a.sm_id=b.sm_id left join Prolist c on a.p_txm=c.pro_txm where " + where; helper.Params.Clear(); //Response.Write(totalSql); DataTable tdt = helper.ExecDataTable(totalSql); totalCount = Convert.ToInt32(tdt.Rows[0]["pcount"]); totalSales = Convert.ToDouble(tdt.Rows[0]["sales"]); totalSales_in = Convert.ToDouble(tdt.Rows[0]["sales_in"]); totalCount_return = Convert.ToInt32(tdt.Rows[0]["rcount"]); }
protected void BindStockList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_FinancialFlow", "*", "id desc", true, AspNetPager1.PageSize, index, where, out count); MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductFinance.aspx?page={0}&" + queryStr; }
protected void bindFactoryPro(int work_id) { SqlHelper helper = LocalSqlHelper.WH; string sql = "SELECT distinct c.* FROM Tb_Working_main a with(nolock) left join Tb_template b on a.tpl_id=b.tpl_id left join Prolist c on b.pro_code=c.pro_code where a.work_id=@work_id order by c.pro_code,c.pro_spec"; // string sql = "SELECT a.*,b.quantity FROM Prolist a left join Tb_Working_main b with(nolock) on a.pro_code=b.pro_code_new where b.work_id=@work_id order by b.wm_id"; helper.Params.Add("work_id", work_id); DataTable dt = helper.ExecDataTable(sql); for (int i = 0; i < dt.Rows.Count; i++) { } MemberList.DataShow(dt); }
protected void bindReturnPro(int direct_id) { SqlHelper helper = LocalSqlHelper.WH; string sql = "SELECT *,shelf_no='' FROM Tb_storage_product with(nolock) where sm_id=@sm_id order by p_id"; helper.Params.Add("sm_id", direct_id); DataTable dt = helper.ExecDataTable(sql); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["shelf_no"] = StorageHelper.getShelfNo(Convert.ToInt32(dt.Rows[i]["pro_id"]), int_from_warehouse_id); } MemberList.DataShow(dt); }
protected void BindStockList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("(select a.*,b.pro_txm from Tb_ChangeStockRecord a left join Product b on a.pro_id=b.pro_id) as xu", "*", "id desc", true, AspNetPager1.PageSize, index, where, out count); MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductStockChange.aspx?page={0}&" + queryStr; }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; DataTable dt = conn.ExecDataTable("select *,shelf_no='' from Tb_storage_product with(nolock) where " + where + " order by p_name,p_spec"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["shelf_no"] = StorageHelper.getShelfNo(Convert.ToInt32(dt.Rows[i]["pro_id"]), warehouse_id); all_num += Convert.ToDouble(dt.Rows[i]["p_quantity"]); all_price += Convert.ToDouble(dt.Rows[i]["p_price"]) * Convert.ToDouble(dt.Rows[i]["p_quantity"]); } MemberList.DataShow(dt); }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPage("wareHouse_Admin a left join SinoRole b on a.RoleID=b.ID", "a.*,b.RoleName", "a.id desc", true, AspNetPager1.PageSize, index, where, out count); if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; }
protected void BindGuideList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_guide_staff", "*", "guide_id desc", true, AspNetPager1.PageSize, index, where, out count); if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_check_input", "*", "id asc", true, AspNetPager1.PageSize, index, where, out count); if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "CheckStockDetail.aspx?page={0}&id=" + main_id; }
protected void BindMemberList(int index) { string where = getWhere(); int count = 0; SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_storage_main with(nolock)", "Tb_storage_main.*,procount=(select sum(p_quantity) from Tb_storage_product with(nolock) where sm_id=Tb_storage_main.sm_id),sku=(select count(p_quantity) from Tb_storage_product with(nolock) where sm_id=Tb_storage_main.sm_id)", "sm_id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { // conn.Params.Clear(); // dt.Rows[i]["procount"] = conn.ExecScalar("select count(p_quantity) from Tb_storage_product with(nolock) where sm_id=" + dt.Rows[i]["sm_id"].ToString()); } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductTran.aspx?page={0}&" + queryStr; }
protected void BindMemberList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Prolist", "*,'' as supplierName,'' as shortSupplierName", "pro_id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["supplierName"] = Warehousing.Business.StorageHelper.getSupplierName(Convert.ToInt32(dt.Rows[i]["pro_supplierid"])); dt.Rows[i]["shortSupplierName"] = Convert.ToString(dt.Rows[i]["supplierName"]).Length > 4 ? Convert.ToString(dt.Rows[i]["supplierName"]).Substring(0, 4) + ".." : Convert.ToString(dt.Rows[i]["supplierName"]); } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductList.aspx?page={0}&" + queryStr; }
protected void BindMemberList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; string table = "(select a.txm,isnull(sum(productcount),0) as pcount,isnull(sum(case when productcount<0 then productcount else 0 end),0) as return_count,pro_name,pro_code,pro_spec,pro_model,supplierName=cast(pro_supplierid as nvarchar(50)),shortSupplierName='',a.danjia,pro_inprice from Direct_OrderDetail a left join Direct_OrderMain b on a.dingdan=b.dingdan left join Prolist p on a.txm=p.pro_txm where " + where + " group by a.txm,pro_name,pro_code,pro_supplierid,pro_spec,pro_model,a.danjia,pro_inprice) as sales"; if (groupby == "huohao") { table = "(select txm='',isnull(sum(productcount),0) as pcount,isnull(sum(case when productcount<0 then productcount else 0 end),0) as return_count,pro_name,pro_code,pro_spec,pro_model='',supplierName=cast(pro_supplierid as nvarchar(50)),shortSupplierName='',a.danjia,pro_inprice from Direct_OrderDetail a left join Direct_OrderMain b on a.dingdan=b.dingdan left join Prolist p on a.txm=p.pro_txm where " + where + " group by pro_code,pro_name,pro_supplierid,pro_spec,a.danjia,pro_inprice) as sales"; DD_GroupBy.Text = groupby; } //Response.Write(table); //Response.End(); DataTable dt = conn.TablesPageNew(table, "*", "pcount desc,txm asc", true, AspNetPager1.PageSize, index, "", out count); // DataTable dt = PublicHelper.TablesPage(conn, table, "*", "pcount desc", AspNetPager1.PageSize, index, "", "txm", out count); for (int i = 0; i < dt.Rows.Count; i++) { if (my_warehouse_id == 0) { dt.Rows[i]["supplierName"] = StorageHelper.getSupplierName(Convert.ToInt32(dt.Rows[i]["supplierName"])); dt.Rows[i]["shortSupplierName"] = Convert.ToString(dt.Rows[i]["supplierName"]).Length > 4 ? Convert.ToString(dt.Rows[i]["supplierName"]).Substring(0, 4) + ".." : Convert.ToString(dt.Rows[i]["supplierName"]); } } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductSales.aspx?page={0}&" + queryStr; string totalSql = "select isnull(sum(productcount),0) as pcount,isnull(sum(case when productcount<0 then productcount else 0 end),0) as rcount,isnull(sum(danjia*productcount),0) as sales,isnull(sum(p.pro_inprice*productcount),0) as sales_in from Direct_OrderDetail a left join Direct_OrderMain b on a.dingdan=b.dingdan left join Prolist p on a.txm=p.pro_txm where " + where; helper.Params.Clear(); DataTable tdt = helper.ExecDataTable(totalSql); totalCount = Convert.ToInt32(tdt.Rows[0]["pcount"]); totalSales = Convert.ToDouble(tdt.Rows[0]["sales"]); totalSales_in = Convert.ToDouble(tdt.Rows[0]["sales_in"]); totalCount_return = Convert.ToInt32(tdt.Rows[0]["rcount"]); }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; // Response.Write(strTable); // Response.End(); DataTable dt = conn.TablesPageNew(strTable, "*,plus_num=check_num-kc_num", "pro_id", true, AspNetPager1.PageSize, index, where, out count); if (dt.Rows.Count == 0) { norecord = 1; } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "CheckStockResult.aspx?page={0}&id=" + main_id; }
protected void BindMemberList() { int ModuleId = 0; int i = 0; string PowerStr = string.Empty; string Psql = string.Empty; SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; string sql = "select *,p1='',p2='',p3='',p4='',p5='',p6='' from SinoModule order by OrderNum,ID desc"; DataTable dt = conn.ExecDataTable(sql); conn.Params.Clear(); if (dt.Rows.Count == 0) { norecord = 1; } else { for (i = 0; i < dt.Rows.Count; i++) { ModuleId = Convert.ToInt32(dt.Rows[i]["ID"]); helper.Params.Clear(); Psql = "select PowerStr from SinoRoleModule where RoleID=@RoleID and ModuleID=@ModuleId"; helper.Params.Add("@RoleID", RoleID); helper.Params.Add("@ModuleId", ModuleId); PowerStr = Convert.ToString(helper.ExecScalar(Psql)); if (PowerStr.IsNullOrEmpty()) { PowerStr = "000000"; } dt.Rows[i]["p1"] = PowerStr.Substring(0, 1) == "1"?" checked":""; dt.Rows[i]["p2"] = PowerStr.Substring(1, 1) == "1"?" checked":""; dt.Rows[i]["p3"] = PowerStr.Substring(2, 1) == "1"?" checked":""; dt.Rows[i]["p4"] = PowerStr.Substring(3, 1) == "1"?" checked":""; dt.Rows[i]["p5"] = PowerStr.Substring(4, 1) == "1"?" checked":""; dt.Rows[i]["p6"] = PowerStr.Substring(5, 1) == "1" ? " checked" : ""; } } MemberList.DataShow(dt); }
protected void BindMemberList(int index) { string where = getWhere(); int count = 0; SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_template with(nolock)", "*,pro_txm_from='',pvolume=0.00,pro_nums=0.00", "tpl_id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { helper.Params.Clear(); dt.Rows[i]["pvolume"] = helper.ExecScalar("select isnull(sum(a.pro_nums*b.pro_inprice),0) from Tb_template_material a with(nolock) left join Prolist b with(nolock) on a.pro_txm_from=b.pro_txm where a.tpl_id=" + dt.Rows[i]["tpl_id"].ToString()); dt.Rows[i]["pro_txm_from"] = helper.ExecScalar("select isnull(pro_txm_from,'') from Tb_template_material where tpl_id=" + dt.Rows[i]["tpl_id"] + " order by id"); dt.Rows[i]["pro_nums"] = helper.ExecScalar("select isnull(pro_nums,0) from Tb_template_material where tpl_id=" + dt.Rows[i]["tpl_id"] + " order by id"); //dt.Rows[i]["pro_color"] = helper.ExecScalar("select isnull(b.pro_txm_from,'') from Tb_template_material a left join Product b on a.pro_txm_from=b.pro_txm where a.tpl_id=" + dt.Rows[i]["tpl_id"]); } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "Template.Aspx?page={0}&" + queryStr; }
protected void BindMemberList(int index, string where) { int count = 0; SqlHelper conn = LocalSqlHelper.WH; DataTable dt = conn.ExecDataTable("select * from Tb_storage_product with(nolock) where " + where); if (dt.Rows.Count == 0) { norecord = 1; } for (int i = 0; i < dt.Rows.Count; i++) { if (p_id_old.IsNullOrEmpty()) { p_id_old = dt.Rows[i]["p_id"].ToString(); } else { p_id_old = p_id_old + "," + dt.Rows[i]["p_id"].ToString(); } } MemberList.DataShow(dt); }
protected void BindMemberList(int index) { string where = getWhere(); int count = 0; SqlHelper conn = LocalSqlHelper.WH; SqlHelper helper = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_storage_main with(nolock)", "Tb_storage_main.*,pvolume=0.00,procount=isnull((select sum(p_quantity) from Tb_storage_product with(nolock) where sm_id=Tb_storage_main.sm_id),0),sku=isnull((select count(p_quantity) from Tb_storage_product with(nolock) where sm_id=Tb_storage_main.sm_id),0) ", "sm_id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { helper.Params.Clear(); dt.Rows[i]["pvolume"] = helper.ExecScalar("select isnull(sum(p_quantity*p_price),0) from Tb_storage_product a with(nolock) where sm_id=" + dt.Rows[i]["sm_id"].ToString()); if (dt.Rows[i]["sm_direction"].ToString() == "入库") { dt.Rows[i]["pvolume"] = 0 - Convert.ToDouble(dt.Rows[i]["pvolume"]); } } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "ProductDoing.aspx?page={0}&" + queryStr; }
protected void BindMemberList(int index) { int count = 0; string where = getWhere(); SqlHelper conn = LocalSqlHelper.WH; SqlHelper conn_2 = LocalSqlHelper.WH; AspNetPager1.PageSize = 20; DataTable dt = conn.TablesPageNew("Tb_User", "*,PayAlready=0.00,PayWill=0.00,PayAll=0.00", "user_id desc", true, AspNetPager1.PageSize, index, where, out count); for (int i = 0; i < dt.Rows.Count; i++) { conn_2.Params.Clear(); //应收款 dt.Rows[i]["PayAll"] = conn_2.ExecScalar("select isnull(sum(a.p_quantity*(case b.sm_type when 8 then 0-a.p_price else a.p_price end)),0) from Tb_storage_product a left join Tb_storage_main b on a.sm_id=b.sm_id where b.sm_type in (8,12,13) and b.sm_status in (1) and b.consumer_id=" + dt.Rows[i]["User_id"]); conn_2.Params.Clear(); dt.Rows[i]["PayAlready"] = conn_2.ExecScalar("select isnull(sum(case b.sm_type when 8 then 0-a.pay_money else a.pay_money end),0) from Tb_FinancialFlow a left join Tb_storage_main b on a.sm_id=b.sm_id where a.is_cancel=0 and b.sm_type in (8,12,13) and b.sm_status in (1) and b.consumer_id=" + dt.Rows[i]["User_id"]); dt.Rows[i]["PayWill"] = Convert.ToDouble(dt.Rows[i]["PayAll"]) - Convert.ToDouble(dt.Rows[i]["PayAlready"]); } MemberList.DataShow(dt); AspNetPager1.RecordCount = count; AspNetPager1.UrlRewritePattern = "UserList.aspx?page={0}&" + queryStr; }