private void addSTXALLOCATION_RPT(string filename, string allocType)
{
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format(" Select * FROM [{1}].[STXALLOCATION] where STXAGRP = N'{0}'", filename, _warehouse);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
conn.Close();
foreach (DataRow dr in ds.Tables[0].Rows)
{
STXCalendar stxcalendar = null;
stxcalendar = getSTXFYQW(dr["STARTDATE"].ToString(), dr["ENDDATE"].ToString());
int sumqty = sumLocationQty(dr["PRISKU"].ToString(), stxcalendar);
//如果sumqty>0 不管
int exist = GetLocationRPT(stxcalendar, dr["PRISKU"].ToString(), dr["SKU"].ToString());
if (allocType == "Q")
{
}
else
{
}
}
}
}
private int sumLocationQty(string psku, STXCalendar calendar)
{
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select isnull(sum(WALLACTUALQTY),0) from [{3}].[STXALLOCATION_RPT] r where r.PRISKU='{0}' and r.FISCALYEAR='{1}' and r.QUARTER='{2}' ",
psku, calendar.Year, calendar.Quarter, _warehouse);
object result = cmd.ExecuteScalar();
conn.Close();
return(Convert.ToInt32(result));
}
}
private int GetLocationRPT(STXCalendar calendar, string psku, string sku)
{
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format("select count(*) from [{4}].[STXALLOCATION_RPT] r where r.PRISKU='{0}' and r.sku='{1}' and r.FISCALYEAR='{2}' and r.QUARTER='{3}' ",
psku, sku, calendar.Year, calendar.Quarter, _warehouse);
object result = cmd.ExecuteScalar();
conn.Close();
return(Convert.ToInt32(result));
}
}
private STXCalendar getSTXFYQW(string startdt, string enddt)
{
STXCalendar stxcalendar = new STXCalendar();
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = string.Format(" select Top 1 FISCALYEAR,QUARTER,WEEK from [{2}].[STXCALENDAR] where CONVERT(datetime,FROM_DATE) >= CONVERT(datetime,'{0}') and CONVERT(datetime,TO_DATE)<=CONVERT(datetime,'{1}')",
startdt, enddt, _warehouse);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
stxcalendar.Year = Convert.ToInt32(reader.GetSqlString(0));
stxcalendar.Quarter = Convert.ToInt32(reader.GetSqlString(1));
stxcalendar.Week = Convert.ToInt32(reader.GetSqlString(2));
}
conn.Close();
}
if (stxcalendar.Quarter == 4 && isMaxWeek(stxcalendar.Year, stxcalendar.Quarter, stxcalendar.Week))
{
stxcalendar.Week = 1;
stxcalendar.Quarter = 1;
stxcalendar.Year = stxcalendar.Year + 1;
}
else if (isMaxWeek(stxcalendar.Year, stxcalendar.Quarter, stxcalendar.Week))
{
stxcalendar.Week = stxcalendar.Week + 1;
stxcalendar.Quarter = stxcalendar.Quarter + 1;
}
stxcalendar.BeginWeek = getBeginWeek(stxcalendar.Year, stxcalendar.Quarter);
return(stxcalendar);
}
private void updateLocRPT(DataRow dr, STXCalendar calendar, string filename)
{
}
private void insertLocRPT(DataRow dr, STXCalendar calendar, string filename)
{
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
#region insert sql
cmd.CommandText = string.Format(@"INSERT INTO [{14}].[STXALLOCATION_RPT]
([WHSEID]
,[STXAGRP]
,[STXAKEY]
,[FISCALYEAR]
,[QUARTER]
,[WEEK]
,[PRISKU]
,[PLANNERCODE]
,[SITE]
,[STORERKEY]
,[SKU]
,[QPERCENT]
,[ADDWHO]
,[begweek]
)
VALUES
('{0}'
,'{1}'
,'{2}'
,'{3}'
,'{4}'
,'{5}'
,'{6}'
,'{7}'
,'{8}'
,'{9}'
,'{10}'
,'{11}'
,'{12}'
,'{13}'
)"
, _warehouse
, filename
, getmaxkey()
, calendar.Year
, calendar.Quarter
, calendar.Week
, dr["PRISKU"].ToString()
, dr["PLANNERCODE"].ToString()
, dr["SITE"].ToString()
, dr["STORERKEY"].ToString()
, dr["SKU"].ToString()
, dr["QPERCENT"].ToString()
, "importservice"
, calendar.BeginWeek
, _warehouse
);
#endregion
cmd.ExecuteNonQuery();
conn.Close();
}
}