public static int UpdateCutomerOrderWithoutCutting(mg_CustomerOrderModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update mg_CustomerOrder set ");
strSql.Append("co_no=@co_no,");
strSql.Append("co_cutomerid=@co_cutomerid ");
strSql.Append(" where co_id=@co_id ");
SqlParameter[] parameters =
{
new SqlParameter("@co_id", SqlDbType.Decimal),
new SqlParameter("@co_no", SqlDbType.NVarChar),
new SqlParameter("@co_cutomerid", SqlDbType.Int)
};
parameters[0].Value = model.co_id;
parameters[1].Value = model.co_no;
parameters[2].Value = model.co_cutomerid;
strSql.Append(" delete from mg_cusOrder_Allpart_rel where co_id=" + model.co_id + "; ");
if (!string.IsNullOrEmpty(model.all_ids))
{
string[] all_idArr = model.all_ids.Split('|');
string[] co_countArr = model.co_counts.Split('|');
string odsStr = @"INSERT INTO [mg_cusOrder_Allpart_rel]
([co_id]
,[all_id]
,[co_count],orderno)
VALUES
($co_id$
,$all_id$
,$co_count$,$orderno$);";
for (int i = 0; i < all_idArr.Length; i++)
{
string key = "";
if ((i + 1) <= co_countArr.Length)
{
key = co_countArr[i];
}
strSql.Append(odsStr.Replace("$all_id$", all_idArr[i]).Replace("$co_id$", model.co_id.ToString()).Replace("$co_count$", key).Replace("$orderno$", (i + 1).ToString()));
}
}
int rows = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, System.Data.CommandType.Text, strSql.ToString(), parameters);
return(rows);
}
void SaveCustomerOrder()
{
string co_id = Request.Params["co_id"];
string co_cutomerid = Request.Params["co_cutomerid"];
string co_no = Request.Params["co_no"];
string all_ids = Request.Params["all_ids"];
string co_counts = Request.Params["co_counts"];
string co_isCutted = Request.Params["co_isCutted"];
mg_CustomerOrderModel model = new mg_CustomerOrderModel();
model.co_id = NumericParse.StringToInt(co_id);
model.co_cutomerid = NumericParse.StringToInt(co_cutomerid);
model.co_no = co_no;
model.all_ids = all_ids;
model.co_counts = co_counts;
model.co_isCutted = NumericParse.StringToInt(co_isCutted);
string json = mg_CustomerOrderBLL.saveCustomerOrder(model);
Response.Write(json);
Response.End();
}
public static string saveCustomerOrder(mg_CustomerOrderModel model)
{
int id = (int)model.co_id;
int isCutted = (int)model.co_isCutted;
if (id != 0 && isCutted == 0) //更新订单信息，不拆单
{
return(UpdateCutomerOrderWithoutCutting(model));
}
else if (id == 0 && isCutted == 0)//新增订单信息，不拆单
{
return(AddCutomerOrderWithoutCutting(model));
}
else if (id != 0 && isCutted != 0)//更新订单信息，拆单 (未完成)
{
return(UpdateCutomerOrderWithCutting(model));
}
else if (id == 0 && isCutted != 0)//新增订单信息，拆单 (未完成)
{
return(AddCutomerOrderWithCutting(model));
}
return("false");
}
//更新订单信息，不拆单
private static string UpdateCutomerOrderWithoutCutting(mg_CustomerOrderModel model)
{
int count = mg_CustomerOrderDAL.UpdateCutomerOrderWithoutCutting(model);
return(count > 0 ? "true" : "false");
}
//更新订单信息，拆单
private static string UpdateCutomerOrderWithCutting(mg_CustomerOrderModel model)
{
throw new NotImplementedException();
}
public static int AddCutomerOrderWithCutting(mg_CustomerOrderModel model)
{
throw new NotImplementedException();
}
public static List <mg_CustomerOrderModel> QueryListForFirstPage(string pagesize, out string total, string isCutted)
{
total = "0";
List <mg_CustomerOrderModel> list = null;
string queryStr = " where co_isCutted=" + isCutted;
string sql1 = @"select count([co_id]) total from [mg_CustomerOrder] cus " + queryStr + @" ;";
string sql2 = @"
with allPart as
(
SELECT [all_id]
,[all_no]
,[all_rateid]
,r.prop_name [all_ratename]
,[all_colorid]
,c.prop_name [all_colorname]
,[all_metaid]
,m.prop_name [all_metaname]
FROM [mg_allpart] a
left join mg_Property c on a.all_colorid = c.prop_id
left join mg_Property r on a.all_rateid = r.prop_id
left join mg_Property m on a.all_metaid = m.prop_id
)
SELECT top " + pagesize + @" cus.[co_id]
,cus.[co_no]
, stuff((select '|' +CAST(all_id as varchar(10)) from mg_cusOrder_Allpart_rel where co_id = cus.co_id order by orderno for xml path('')),1,1,'')as all_ids
, stuff((select '|' +CAST(co_count as varchar(10)) from mg_cusOrder_Allpart_rel where co_id = cus.co_id order by orderno for xml path('')),1,1,'')as co_counts
, stuff((select '、' + (all_no+' ('+ap.all_ratename+'+'+ap.all_metaname+'+'+ap.all_colorname+') '+CAST(co_count as varchar(10))+' 套') from allPart ap inner join mg_cusOrder_Allpart_rel ca on ap.all_id=ca.all_id and co_id = cus.co_id order by ca.orderno for xml path('')),1,1,'')as appPartdesc
, stuff((select '|' +CAST(ap.all_id as varchar(10))+','+CAST(co_count as varchar(10))+','+all_no from mg_cusOrder_Allpart_rel car left join allPart ap on ap.all_id=car.all_id where co_id = cus.co_id order by car.orderno for xml path('')),1,1,'')as idcounts
,[co_cutomerid]
,p.prop_name [co_customer]
,[co_state]
,[co_order]
,[co_isCutted]
FROM [mg_CustomerOrder] cus
left join mg_Property p on cus.co_cutomerid = p.prop_id
" + queryStr + @"
order by cus.co_order desc
";
DataSet ds = SqlHelper.GetDataSetTableMapping(SqlHelper.SqlConnString, System.Data.CommandType.Text, sql1 + sql2, new string[] { "count", "data" }, null);
if (DataHelper.HasData(ds))
{
DataTable dt1 = ds.Tables["count"];
total = DataHelper.GetCellDataToStr(dt1.Rows[0], "total");
DataTable dt2 = ds.Tables["data"];
list = new List <mg_CustomerOrderModel>();
foreach (DataRow row in dt2.Rows)
{
mg_CustomerOrderModel model = new mg_CustomerOrderModel();
model.co_id = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "co_id"));
model.co_no = DataHelper.GetCellDataToStr(row, "co_no");
model.co_isCutted = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "co_isCutted"));
model.co_cutomerid = NumericParse.StringToInt(DataHelper.GetCellDataToStr(row, "co_cutomerid"));
model.all_ids = DataHelper.GetCellDataToStr(row, "all_ids");
model.co_counts = DataHelper.GetCellDataToStr(row, "co_counts");
model.appPartdesc = DataHelper.GetCellDataToStr(row, "appPartdesc");
model.idcounts = DataHelper.GetCellDataToStr(row, "idcounts");
list.Add(model);
}
}
return(list);
}
public static int AddCutomerOrderWithoutCutting(mg_CustomerOrderModel model)
{
string maxid = @"declare @i int;declare @o int;
SELECT @i=max([co_id]) FROM [mg_CustomerOrder];
if @i is null
begin
set @i=1
end
else
begin
set @i=@i+1
end;
SELECT @o=max([co_order]) FROM [mg_CustomerOrder];
if @o is null
begin
set @o=1
end
else
begin
set @o=@o+1
end;
";
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into mg_CustomerOrder(");
strSql.Append("co_id,co_no,co_cutomerid,co_state,co_order,co_isCutted,co_createtime)");
strSql.Append(" values (");
strSql.Append("@i,@co_no,@co_cutomerid,0,@o,@co_isCutted,@co_createtime);");
SqlParameter[] parameters =
{
new SqlParameter("@co_no", SqlDbType.NVarChar),
new SqlParameter("@co_cutomerid", SqlDbType.Int),
new SqlParameter("@co_isCutted", SqlDbType.Int),
new SqlParameter("@co_createtime", SqlDbType.DateTime)
};
parameters[0].Value = model.co_no;
parameters[1].Value = model.co_cutomerid;
parameters[2].Value = model.co_isCutted;
parameters[3].Value = DateTime.Now;
if (!string.IsNullOrEmpty(model.all_ids)) //订单：点击保存时候触发的
{
string[] all_idArr = model.all_ids.Split('|');
string[] co_countArr = model.co_counts.Split('|');
string odsStr = @"INSERT INTO [mg_cusOrder_Allpart_rel]
([co_id]
,[all_id]
,[co_count]
,orderno)
VALUES
($co_id$
,$all_id$
,$co_count$
,$orderno$);";
for (int i = 0; i < all_idArr.Length; i++)
{
string key = "";
if ((i + 1) <= co_countArr.Length)
{
key = co_countArr[i];
}
strSql.Append(odsStr.Replace("$all_id$", all_idArr[i]).Replace("$co_id$", "@i").Replace("$co_count$", key)).Replace("$orderno$", (i + 1).ToString());
}
}
int rows = SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnString, System.Data.CommandType.Text, maxid + strSql, parameters);
return(rows);
// return 0;
}
