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;
}
