public static string SearchData(String history, String fdate, String tdate) { string res = string.Empty; try { string sql = ""; if (history == "1") { sql = "SELECT * FROM [FGA_EDIOrder_List] WHERE [Ship_Date] >= cast('" + fdate + "' as datetime) and [Ship_Date] < DATEADD(DAY,1 ,cast('" + tdate + "' as datetime)) " + " order by [Ship_Date],[MasterID],[JOB_SEQUENCE] "; } else { sql = "SELECT * FROM [FGA_EDIOrder_List] WHERE isnull(rstatus,0) = 0 " + " order by [Ship_Date],[MasterID],[JOB_SEQUENCE] "; } DataSet ds = new DataSet(); ds = FGA_DAL.Base.SQLServerHelper.Query(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { List <EDIOrderListModel> luw = new List <EDIOrderListModel>(); foreach (DataRow row in ds.Tables[0].Rows) { EDIOrderListModel ERM = new EDIOrderListModel(row); luw.Add(ERM); } JavaScriptSerializer jssl = new JavaScriptSerializer(); res = jssl.Serialize(luw); res = res.Replace("\\/Date(", "").Replace(")\\/", ""); } } catch (Exception e) { } return(res); }
public static string SearchData() { //按用户查看EDI的数据 UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER]; string ET = ""; string sql = ""; if (model.USERNAME == "Honda_front") { ET = "Front"; } if (model.USERNAME == "Honda_rear") { ET = "Rear"; } if (model.USERNAME == "Honda_side") { ET = "Side"; } string res = string.Empty; try { if (model.USERNAME != "administrator") { sql = "SELECT [customer_name],[Customer_Address_Code],[Customer_Part_No],[Customer_Part_Revision] " + " ,[part_no],[Due_Date] ,[Ship_Date],[ORDER_NO] ,[Lot_No],[BATCH_NO]" + " ,[Standard_Quantity],[Quantity],[JOB_SEQUENCE],[EDI_RowID],[MasterID],isnull([LastEditDate],[CreateDate]) as [CreateDate],[PartType],[IsConfirm] " + " FROM [FGA_EDI_862_T] where isnull(rstatus,0) = 0 and PARTTYPE = '" + ET + "' and isnull(IsConfirm,0) = 1 " + "order by [Ship_Date],[MasterID],[JOB_SEQUENCE]"; } if (model.USERNAME == "administrator" || model.USERNAME == "fy.hxu") { sql = "SELECT [customer_name],[Customer_Address_Code],[Customer_Part_No],[Customer_Part_Revision] " + " ,[part_no],[Due_Date] ,[Ship_Date],[ORDER_NO] ,[Lot_No],[BATCH_NO]" + " ,[Standard_Quantity],[Quantity],[JOB_SEQUENCE],[EDI_RowID],[MasterID],isnull([LastEditDate],[CreateDate]) as [CreateDate],[CreateDate],[PartType],[IsConfirm] " + " FROM [FGA_EDI_862_T] where isnull(rstatus,0) = 0 " + "order by [Ship_Date],[MasterID],[JOB_SEQUENCE]"; } DataSet ds = new DataSet(); ds = FGA_DAL.Base.SQLServerHelper.Query(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { List <EDIOrderListModel> luw = new List <EDIOrderListModel>(); foreach (DataRow row in ds.Tables[0].Rows) { EDIOrderListModel ERM = new EDIOrderListModel(row); luw.Add(ERM); } JavaScriptSerializer jssl = new JavaScriptSerializer(); res = jssl.Serialize(luw); res = res.Replace("\\/Date(", "").Replace(")\\/", ""); } } catch (Exception e) { } return(res); }
public static string MergeData() { string res = "0"; UsersModel model = (UsersModel)HttpContext.Current.Session[SysConst.S_LOGIN_USER]; string sql = "SELECT * FROM [FGA_EDIOrder_List] WHERE isnull(rstatus,0) = 0 " + "order by [Ship_Date],[MasterID],[JOB_SEQUENCE] "; DataSet ds = new DataSet(); ds = FGA_DAL.Base.SQLServerHelper.Query(sql); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { List <EDIOrderListModel> luw = new List <EDIOrderListModel>(); int pcycle = 0; for (int i = 0; i < ds.Tables[0].Rows.Count; i = i + 1 + pcycle) { EDIOrderListModel ERM = new EDIOrderListModel(ds.Tables[0].Rows[i]); int count = ERM.Quantity; pcycle = 0; if (i + 1 < ds.Tables[0].Rows.Count) { for (int j = i + 1; j < ds.Tables[0].Rows.Count; j++) { EDIOrderListModel ERM_Next = new EDIOrderListModel(ds.Tables[0].Rows[j]); if (ERM.MasterID == ERM_Next.MasterID) { if (ERM.Part_NO == ERM_Next.Part_NO) { count = count + ERM_Next.Quantity; pcycle++; if (j + 1 == ds.Tables[0].Rows.Count) { ERM.Quantity = count; luw.Add(ERM); } } else { ERM.Quantity = count; luw.Add(ERM); break; } } else { ERM.Quantity = count; luw.Add(ERM); break; } } } else { ERM.Quantity = count; luw.Add(ERM); } } if (luw.Count > 0) { List <String> sqllist = new List <String>(); foreach (EDIOrderListModel vo in luw) { string insertsql = "insert into [FGA_EDI_862_T]([customer_name],[Customer_Address_Code],[Customer_Part_Revision],[Customer_Part_No],[part_no],[part_name]" + ",[Due_Date],[Ship_Date],[ORDER_NO],[Lot_No],[BATCH_NO],[Standard_Quantity],[Quantity],[JOB_SEQUENCE],[rstatus],[MasterID],[EDI_RowID],[IsConfirm],[Creator],[CreateDate])" + "values('Honda North America','" + vo.Customer_Address_Code + "','" + vo.Customer_Part_Revision + "','" + vo.Customer_Part_NO + "','" + vo.Part_NO + "'," + "'" + vo.Part_Name + "','" + vo.Due_Date + "','" + vo.Ship_Date + "','" + vo.Order_NO + "','" + vo.Lot_NO + "','" + vo.Batch_NO + "'," + vo.Standard_Quantity + "," + "" + vo.Quantity + ",'" + vo.Job_Sequence + "','0','" + vo.MasterID + "',NEXT VALUE FOR OEM_OrderKey_seq,0,'" + model.USERNAME + "',getdate())"; sqllist.Add(insertsql); } string sqlupdate = "update [FGA_EDI_862_T] SET [FGA_EDI_862_T].PartType = [SmallLot_PartType_T].[PartType] " + "FROM [FGA_EDI_862_T] ,[SmallLot_PartType_T] WHERE[FGA_EDI_862_T].Part_NO = [SmallLot_PartType_T].[PartNO] " + "AND ISNULL([FGA_EDI_862_T].PartType,'') = ''"; sqllist.Add(sqlupdate); sqllist.Add("UPDATE [FGA_EDIOrder_List] set [rstatus] = 1,[UpdateDate] = getdate(),[UpdateBy] = '" + model.USERNAME + "' where isnull(rstatus,0) = 0"); if (FGA_DAL.Base.SQLServerHelper.ExecuteSqlTran(sqllist) > 0) { res = "1"; } } } return(res); ////获取EDI数据,自动获取老逻辑(销售不需加工数据) //string sql = "SELECT * FROM [FGA_EDIOrder_List] WHERE isnull(rstatus,0) = 0 " + // "order by Customer_Name,Ship_Date,SUBSTRING(Customer_Part_NO,1,7),BATCH_NO,Standard_Quantity,Job_Sequence "; //DataSet ds = new DataSet(); //ds = FGA_DAL.Base.SQLServerHelper.Query(sql); //if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) //{ // List<EDIOrderListModel> luw = new List<EDIOrderListModel>(); // int pcycle = 0; // int groupqty = 0; // int newQty = 0; // int sqty = 0; // bool newGroup = true; // string mid = ""; // string pdate = DateTime.Now.Year.ToString().Substring(2, 2) // + (DateTime.Now.Month.ToString().Length == 1 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString()) // + (DateTime.Now.Day.ToString().Length == 1 ? "0" + DateTime.Now.Day.ToString() : DateTime.Now.Day.ToString()); // for (int i = 0; i < ds.Tables[0].Rows.Count; i = i+1+pcycle) { // pcycle = 0; // EDIOrderListModel ERM = new EDIOrderListModel(ds.Tables[0].Rows[i]); // int count = ERM.Quantity; // if (newQty != 0) // count = ERM.Quantity = newQty; // if (newGroup) // { // groupqty = 0; // sqty = ERM.Standard_Quantity; // mid = pdate + i; // } // if (i + 1 < ds.Tables[0].Rows.Count) // { // for (int j = i + 1; j < ds.Tables[0].Rows.Count; j++) // { // EDIOrderListModel ERM_Next = new EDIOrderListModel(ds.Tables[0].Rows[j]); // if (ERM.Customer_Name == ERM_Next.Customer_Name && ERM.Ship_Date == ERM_Next.Ship_Date && // ERM.Batch_NO == ERM_Next.Batch_NO && ERM.Standard_Quantity == ERM_Next.Standard_Quantity && // ERM.Customer_Part_NO.Substring(0, 7) == ERM_Next.Customer_Part_NO.Substring(0, 7)) // { // if (ERM.Part_NO == ERM_Next.Part_NO) // { // //首先判断是否新组合 // if (newGroup) // { // if (count + ERM_Next.Quantity < ERM_Next.Standard_Quantity) // { // count = count + ERM_Next.Quantity; // newGroup = false; // pcycle++; // newQty = 0; // continue; // } // if (count + ERM_Next.Quantity == ERM_Next.Standard_Quantity) // { // pcycle++; // newQty = 0; // break; // } // if (count + ERM_Next.Quantity > ERM_Next.Standard_Quantity) // { // count = count + ERM_Next.Quantity - ERM_Next.Standard_Quantity; // newGroup = false; // newQty = 0; // pcycle++; // } // } // else // { // if (groupqty + count > sqty) // { // ERM.Quantity = sqty - groupqty; // newQty = count - (sqty - groupqty); // ERM.MasterID = mid; // luw.Add(ERM); // pcycle--; // newGroup = true; // break; // } // else // { // if (groupqty + count == sqty) // { // ERM.Quantity = count; // ERM.MasterID = mid; // groupqty = groupqty + count; // newGroup = true; // luw.Add(ERM); // pcycle++; // newQty = 0; // break; // } // else // { // count = count + ERM_Next.Quantity; // newGroup = false; // pcycle++; // newQty = 0; // continue; // } // } // } // } // else // { // ERM.Quantity = count; // ERM.MasterID = mid; // groupqty = groupqty + count; // if(groupqty != sqty) // newGroup = false; // else // newGroup = true; // luw.Add(ERM); // newQty = 0; // break; // } // } // else // { // ERM.Quantity = count; // ERM.MasterID = mid; // groupqty = groupqty + count; // if (groupqty != sqty) // newGroup = false; // else // newGroup = true; // luw.Add(ERM); // newQty = 0; // break; // } // } // } // else { // ERM.Quantity = count; // ERM.MasterID = mid; // luw.Add(ERM); // newQty = 0; // } // } // if (luw.Count > 0) // { // List<String> sqllist = new List<String>(); // string delsql = "delete from [FGA_EDI_862_T] WHERE 1=2"; // sqllist.Add(delsql); // foreach (EDIOrderListModel vo in luw) { // string insertsql = "insert into [FGA_EDI_862_T]([customer_name],[Customer_Address_Code],[Customer_Part_Revision],[Customer_Part_No],[part_no],[part_name]" + // ",[Due_Date],[Ship_Date],[ORDER_NO],[Lot_No],[BATCH_NO],[Standard_Quantity],[Quantity],[JOB_SEQUENCE],[rstatus],[MasterID],[EDI_RowID],[Creator],[CreateDate])" + // "values('Honda North America','" + vo.Customer_Address_Code+ "','"+vo.Customer_Part_Revision+"','" + vo.Customer_Part_NO + "','" + vo.Part_NO + "'," + // "'"+ vo.Part_Name + "','" + vo.Due_Date + "','" + vo.Ship_Date + "','"+vo.Order_NO+"','" + vo.Lot_NO + "','" + vo.Batch_NO + "',"+vo.Standard_Quantity+"," + // ""+vo.Quantity+",'"+vo.Job_Sequence+"','0','"+vo.MasterID+ "',NEXT VALUE FOR OEM_OrderKey_seq,'"+ model.USERNAME + "',getdate())"; // sqllist.Add(insertsql); // } // sqllist.Add("DELETE FROM FGA_EDI_862_T WHERE Standard_Quantity = Quantity"); // string sqlupdate = "update [FGA_EDI_862_T] SET [FGA_EDI_862_T].PartType = [SmallLot_PartType_T].[PartType] " + // "FROM [FGA_EDI_862_T] ,[SmallLot_PartType_T] WHERE[FGA_EDI_862_T].Part_NO = [SmallLot_PartType_T].[PartNO] " + // "AND ISNULL([FGA_EDI_862_T].PartType,'') = ''"; // sqllist.Add(sqlupdate); // sqllist.Add("UPDATE [FGA_EDIOrder_List] set [rstatus] = 1,[UpdateDate] = getdate(),[UpdateBy] = '"+ model.USERNAME+ "' where isnull(rstatus,0) = 0"); // FGA_DAL.Base.SQLServerHelper.ExecuteSqlTran(sqllist); // } //} //return res; }