public virtual PageInfo GetPageInfo(QueryInfo Query)
{
int RecordCount, PageCount, CurrentPage;
string SqlCount, SqlParam, SqlTable;
PageInfo Page;
try
{
Page = new PageInfo();
SqlTable = "Product_Review";
SqlParam = DBHelper.GetSqlParam(Query.ParamInfos);
SqlCount = "SELECT COUNT(Product_Review_ID) FROM " + SqlTable + SqlParam;
RecordCount = Tools.NullInt(DBHelper.ExecuteScalar(SqlCount));
PageCount = Tools.CalculatePages(RecordCount, Query.PageSize);
CurrentPage = Tools.DeterminePage(Query.CurrentPage, PageCount);
Page.RecordCount = RecordCount;
Page.PageCount = PageCount;
Page.CurrentPage = CurrentPage;
Page.PageSize = Query.PageSize;
return(Page);
}
catch (Exception ex)
{
throw ex;
}
}
public long InsertUserDetails(UserModel UserDetails)
{
try
{
SqlParameter[] parms = new SqlParameter[11];
parms[0] = new SqlParameter("@UserID", UserDetails.UserID);
parms[1] = new SqlParameter("@FirstName", UserDetails.FirstName);
parms[2] = new SqlParameter("@LastName", UserDetails.LastName);
parms[3] = new SqlParameter("@Email", UserDetails.Email);
parms[4] = new SqlParameter("@UserTypeID", UserDetails.UserTypeID);
parms[5] = new SqlParameter("@Password", UserDetails.Password);
parms[6] = new SqlParameter("@IsRegistrationApproved", UserDetails.IsRegistrationApproved);
parms[7] = new SqlParameter("@IsDeleted", UserDetails.IsDeleted);
parms[8] = new SqlParameter("@Qalification", UserDetails.Qalification);
parms[9] = new SqlParameter("@Experience", UserDetails.Experience);
parms[10] = new SqlParameter("@VPath", UserDetails.VPath);
if (base.Transaction != null)
{
return((int)_helper.ExecuteScalar(base.Transaction, CommandType.StoredProcedure, "InsertUserDetails", parms));
}
else
{
return((int)_helper.ExecuteScalar(base.ConnectionString, CommandType.StoredProcedure, "InsertUserDetails", parms));
}
}
finally { }
}
public string GetDepots()
{
int Depot_ID, Depot_Sort;
string Depot_Name;
int PageSize = tools.CheckInt(Request["rows"]);
int CurrentPage = tools.CheckInt(Request["page"]);
int RecordCount = 0;
int PageCount = 0;
string SqlField = "Depot_ID, Depot_Name, Depot_Sort";
string SqlTable = "SCM_Depot";
string SqlOrder = "ORDER BY " + tools.CheckStr(Request["sidx"]) + " " + tools.CheckStr(Request["sord"]);
string SqlParam = " WHERE Depot_ID > 0";
string SqlCount = "SELECT COUNT(Depot_ID) FROM " + SqlTable + " " + SqlParam;
SqlDataReader RdrList = null;
try {
RecordCount = (int)DBHelper.ExecuteScalar(SqlCount);
PageCount = tools.CalculatePages(RecordCount, PageSize);
CurrentPage = tools.DeterminePage(CurrentPage, PageCount);
string SqlList = DBHelper.GetSqlPage(SqlTable, SqlField, SqlParam, SqlOrder, CurrentPage, PageSize);
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"page\":" + CurrentPage + ",\"total\":" + PageCount + ",\"records\":" + RecordCount + ",\"rows\"");
jsonBuilder.Append(":[");
RdrList = DBHelper.ExecuteReader(SqlList);
while (RdrList.Read())
{
Depot_ID = tools.NullInt(RdrList["Depot_ID"]);
Depot_Name = tools.NullStr(RdrList["Depot_Name"]);
Depot_Sort = tools.NullInt(RdrList["Depot_Sort"]);
jsonBuilder.Append("{\"Depot_ID\":" + Depot_ID + ",\"cell\":[");
//各字段
jsonBuilder.Append("\"");
jsonBuilder.Append(Depot_ID);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Depot_Name));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Depot_Sort);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
if (Public.CheckPrivilege("8fad33fd-ec7f-47e4-bb94-0aca729a5e46"))
{
jsonBuilder.Append("<img src=\\\"/images/icon_edit.gif\\\" alt=\\\"修改\\\"> <a href=\\\"depot_add.aspx?action=renew&depot_id=" + Depot_ID + "\\\" title=\\\"修改\\\">修改</a>");
}
if (Public.CheckPrivilege("0a198c2c-1dda-4ead-b195-c4083a7cc9fd"))
{
jsonBuilder.Append(" <img src=\\\"/images/icon_del.gif\\\" alt=\\\"删除\\\"> <a href=\\\"javascript:void(0);\\\" onclick=\\\"confirmdelete('depot_do.aspx?action=move&depot_id=" + Depot_ID + "')\\\" title=\\\"删除\\\">删除</a>");
}
jsonBuilder.Append("\"");
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return(jsonBuilder.ToString());
}
catch (Exception ex) {
throw ex;
return("");
}
finally {
if (RdrList != null)
{
RdrList.Close();
RdrList = null;
}
}
}
public string GetPurchasings()
{
int Purchasing_ID, Purchasing_Type, Purchasing_DepotID, Purchasing_SupplierID, Purchasing_Amount, Purchasing_Checkout;
string Purchasing_ProductCode, Purchasing_BatchNumber, Purchasing_Operator, Product_IDArry, Purchasing_Tradetime;
double Purchasing_Price, Purchasing_TotalPrice;
int PageSize = tools.CheckInt(Request["rows"]);
int CurrentPage = tools.CheckInt(Request["page"]);
int RecordCount = 0;
int PageCount = 0;
Product_IDArry = "'0'";
Purchasing_Type = tools.CheckInt(Request.QueryString["Purchasing_Type"]);
Purchasing_ProductCode = tools.CheckStr(Request.QueryString["Product_Code"]);
string SqlField = "Purchasing_ID, Purchasing_DepotID, Purchasing_SupplierID, Purchasing_ProductCode, Purchasing_Price, Purchasing_Amount, Purchasing_TotalPrice, Purchasing_BatchNumber, Purchasing_Operator, Purchasing_Checkout, Purchasing_Tradetime, Product_Name, Product_Spec, Product_Maker";
string SqlTable = "SCM_Purchasing LEFT JOIN Product_Basic ON SCM_Purchasing.Purchasing_ProductCode = Product_Basic.Product_Code";
string SqlOrder = "ORDER BY " + tools.CheckStr(Request["sidx"]) + " " + tools.CheckStr(Request["sord"]);
string SqlParam = " ";
if (Purchasing_Type < 3)
{
SqlParam = SqlParam + " WHERE SCM_Purchasing.Purchasing_IsNoStock = 0 and SCM_Purchasing.Purchasing_Type = " + Purchasing_Type;
}
else
{
SqlParam = SqlParam + " WHERE SCM_Purchasing.Purchasing_Type = " + Purchasing_Type;
}
if (Purchasing_ProductCode.Length > 0)
{
SqlParam += " AND (Purchasing_ProductCode LIKE '%" + Purchasing_ProductCode + "%' OR Product_Basic.Product_Name LIKE '%" + Purchasing_ProductCode + "%' OR Product_Basic.Product_Maker LIKE '%" + Purchasing_ProductCode + "%')";
}
string SqlCount = "SELECT COUNT(SCM_Purchasing.Purchasing_ID) FROM " + SqlTable + " " + SqlParam;
SqlDataReader RdrList = null;
try
{
RecordCount = (int)DBHelper.ExecuteScalar(SqlCount);
PageCount = tools.CalculatePages(RecordCount, PageSize);
CurrentPage = tools.DeterminePage(CurrentPage, PageCount);
string SqlList = DBHelper.GetSqlPage(SqlTable, SqlField, SqlParam, SqlOrder, CurrentPage, PageSize);
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"page\":" + CurrentPage + ",\"total\":" + PageCount + ",\"records\":" + RecordCount + ",\"rows\"");
jsonBuilder.Append(":[");
RdrList = DBHelper.ExecuteReader(SqlList);
while (RdrList.Read())
{
Purchasing_ID = tools.NullInt(RdrList["Purchasing_ID"]);
Purchasing_DepotID = tools.NullInt(RdrList["Purchasing_DepotID"]);
Purchasing_SupplierID = tools.NullInt(RdrList["Purchasing_SupplierID"]);
Purchasing_ProductCode = tools.NullStr(RdrList["Purchasing_ProductCode"]);
Purchasing_Price = tools.NullDbl(RdrList["Purchasing_Price"]);
Purchasing_Amount = tools.NullInt(RdrList["Purchasing_Amount"]);
Purchasing_TotalPrice = tools.NullDbl(RdrList["Purchasing_TotalPrice"]);
Purchasing_BatchNumber = tools.NullStr(RdrList["Purchasing_BatchNumber"]);
Purchasing_Operator = tools.NullStr(RdrList["Purchasing_Operator"]);
Purchasing_Checkout = tools.NullInt(RdrList["Purchasing_Checkout"]);
Purchasing_Tradetime = tools.NullDate(RdrList["Purchasing_Tradetime"]).ToShortDateString();
jsonBuilder.Append("{\"Purchasing_ID\":" + Purchasing_ID + ",\"cell\":[");
//各字段
jsonBuilder.Append("\"");
jsonBuilder.Append(Purchasing_ID);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(depot.GetNameByID(Purchasing_DepotID)));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(supplier.GetNameByID(Purchasing_SupplierID)));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Purchasing_ProductCode));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(tools.NullStr(RdrList["Product_Name"])));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(tools.NullStr(RdrList["Product_Spec"])));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(tools.NullStr(RdrList["Product_Maker"])));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.DisplayCurrency(Purchasing_Price));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Purchasing_Amount);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.DisplayCurrency(Purchasing_TotalPrice));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Purchasing_BatchNumber));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Purchasing_Tradetime);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Purchasing_Operator);
jsonBuilder.Append("\",");
if (Purchasing_Checkout == 1)
{
jsonBuilder.Append("\"已结款\",");
}
else
{
jsonBuilder.Append("\"未结款\",");
}
jsonBuilder.Append("\"");
jsonBuilder.Append("<img src=\\\"/images/icon_view.gif\\\" alt=\\\"查看\\\"> <a href=\\\"purchasing_view.aspx?purchasing_id=" + Purchasing_ID + "\\\" title=\\\"查看\\\">查看</a>");
if (Public.CheckPrivilege("a56c96f7-fb31-4944-a248-45a8ad3c4398/a133f0cd-9a5e-4d02-ad94-9e0c0424d66d/87a8726b-5113-46ec-845c-6bd377935196/bfc31928-a7f1-45e0-bd3e-88a4268593ce"))
{
jsonBuilder.Append("<img src=\\\"/images/icon_edit.gif\\\" alt=\\\"修改\\\"> <a href=\\\"purchasing_edit.aspx?purchasing_id=" + Purchasing_ID + "\\\" title=\\\"修改\\\">修改</a>");
}
//if (Public.CheckPrivilege("6b56546f-b64b-4365-85dd-e053f0a51630/598449ed-90f9-4211-bdd5-d471d8fda8a0/692dd604-e428-40cf-9202-43d333245b1d/7c806dd5-9a9a-4e83-bdec-10b328491c1a"))
//{
// jsonBuilder.Append(" <img src=\\\"/images/icon_del.gif\\\" alt=\\\"删除\\\"> <a href=\\\"javascript:void(0);\\\" onclick=\\\"confirmdelete('purchasing_do.aspx?action=move&purchasing_type=" + Purchasing_Type + "&purchasing_id=" + Purchasing_ID + "')\\\" title=\\\"删除\\\">删除</a>");
//}
jsonBuilder.Append("\"");
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return(jsonBuilder.ToString());
}
catch (Exception ex)
{
throw ex;
return("");
}
finally
{
if (RdrList != null)
{
RdrList.Close();
RdrList = null;
}
}
}
/// <summary>
/// 销售量(额)排名
/// </summary>
/// <returns></returns>
public string SalesAmountRanked()
{
string orders_status = tools.CheckStr(Request["orders_status"]);
DateTime startDate;
if (!DateTime.TryParse(tools.CheckStr(Request["startdate"]), out startDate))
{
startDate = DateTime.Today.AddDays(-15);
}
DateTime endDate;
if (!DateTime.TryParse(tools.CheckStr(Request["endDate"]), out endDate))
{
endDate = DateTime.Today;
}
int PageSize = tools.CheckInt(Request["rows"]);
int CurrentPage = tools.CheckInt(Request["page"]);
int RecordCount = 0;
int PageCount = 0;
string SqlField = "G.Orders_Goods_Product_ID, SUM(Orders_Goods_Amount) AS saleCount, SUM(Orders_Goods_Product_Price * Orders_Goods_Amount) AS saleSum";
string SqlTable = "Orders_Goods AS G INNER JOIN Orders AS O ON G.Orders_Goods_OrdersID = O.Orders_ID";
string SqlOrder = "ORDER BY " + tools.CheckStr(Request["sidx"]) + " " + tools.CheckStr(Request["sord"]);
string SqlParam = " WHERE O.Orders_Site = 'CN' AND Orders_SupplierID = " + tools.NullInt(Session["supplier_id"]);
SqlParam += " AND DATEDIFF(d, O.Orders_Addtime, '" + startDate + "') <= 0 AND DATEDIFF(d, O.Orders_Addtime, '" + endDate + "') >= 0";
if (orders_status == "confirm")
{
SqlParam += " AND (Orders_Status > 0 AND Orders_Status < 3)";
}
if (orders_status == "success")
{
SqlParam += " AND Orders_Status = 2";
}
string SqlCount = "SELECT COUNT(DISTINCT Orders_Goods_Product_ID) FROM " + SqlTable + " " + SqlParam;
DataTable DtList = null;
try
{
RecordCount = (int)DBHelper.ExecuteScalar(SqlCount);
PageCount = tools.CalculatePages(RecordCount, PageSize);
CurrentPage = tools.DeterminePage(CurrentPage, PageCount);
string SqlList = "SELECT * FROM (SELECT " + SqlField + " , ROW_NUMBER() OVER (" + SqlOrder + ") AS RowNumber FROM " + SqlTable + " " + SqlParam + " GROUP BY (G.Orders_Goods_Product_ID) ) AS T WHERE RowNumber > " + (CurrentPage - 1) * PageSize + " AND RowNumber < " + ((CurrentPage * PageSize) + 1);
int Product_ID, saleCount, Supplier_ID;
string Product_Name, Product_Code, Product_Spec, Supplier_Name, Cate_Name;
double saleSum;
DtList = DBHelper.Query(SqlList);
if (DtList.Rows.Count > 0)
{
ProductInfo pEntity;
SupplierInfo sEntity;
CategoryInfo cEntity;
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"page\":" + CurrentPage + ",\"total\":" + PageCount + ",\"records\":" + RecordCount + ",\"rows\"");
jsonBuilder.Append(":[");
foreach (DataRow DrList in DtList.Rows)
{
Product_ID = tools.NullInt(DrList["Orders_Goods_Product_ID"]);
saleCount = tools.NullInt(DrList["saleCount"]);
saleSum = tools.NullDbl(DrList["saleSum"]);
pEntity = product.GetProductByID(Product_ID);
if (pEntity != null)
{
Product_Name = pEntity.Product_Name;
//Product_Code = pEntity.Product_Code;
Product_Spec = pEntity.Product_Spec;
Supplier_ID = pEntity.Product_SupplierID;
sEntity = supplier.GetSupplierByID(Supplier_ID);
if (sEntity != null)
{
Supplier_Name = sEntity.Supplier_CompanyName;
}
else
{
Supplier_Name = Supplier_ID.ToString();
}
sEntity = null;
cEntity = category.GetCategoryByID(pEntity.Product_CateID, pub.CreateUserPrivilege("2883de94-8873-4c66-8f9a-75d80c004acb"));
if (cEntity != null)
{
Cate_Name = cEntity.Cate_Name;
}
else
{
Cate_Name = pEntity.Product_CateID.ToString();
}
}
else
{
Product_Name = "";
//Product_Code = "";
Product_Spec = "";
Supplier_Name = string.Empty;
Cate_Name = string.Empty;
}
pEntity = null;
jsonBuilder.Append("{\"id\":" + Product_ID + ",\"cell\":[");
//各字段
jsonBuilder.Append("\"");
jsonBuilder.Append(Product_ID);
jsonBuilder.Append("\",");
//jsonBuilder.Append("\"");
//jsonBuilder.Append(JsonStr(Product_Code));
//jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(JsonStr(Product_Name));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(JsonStr(Supplier_Name));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(JsonStr(Cate_Name));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(JsonStr(Product_Spec));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(saleCount);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(pub.FormatCurrency(saleSum));
jsonBuilder.Append("\",");
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return(jsonBuilder.ToString());
}
else
{
return("");
}
}
catch (Exception ex)
{
throw ex;
return("");
}
finally
{
if (DtList != null)
{
DtList.Dispose();
DtList = null;
}
}
}
public string GetSuppliers()
{
int Supplier_ID;
string Supplier_Name, Supplier_Address, Supplier_Tel, Supplier_Contact;
int PageSize = tools.CheckInt(Request["rows"]);
int CurrentPage = tools.CheckInt(Request["page"]);
int RecordCount = 0;
int PageCount = 0;
string SqlField = "Supplier_ID, Supplier_Name, Supplier_Address, Supplier_Tel, Supplier_Contact";
string SqlTable = "SCM_Supplier";
string SqlOrder = "ORDER BY " + tools.CheckStr(Request["sidx"]) + " " + tools.CheckStr(Request["sord"]);
string SqlParam = " WHERE Supplier_ID > 0";
string SqlCount = "SELECT COUNT(Supplier_ID) FROM " + SqlTable + " " + SqlParam;
SqlDataReader RdrList = null;
try {
RecordCount = (int)DBHelper.ExecuteScalar(SqlCount);
PageCount = tools.CalculatePages(RecordCount, PageSize);
CurrentPage = tools.DeterminePage(CurrentPage, PageCount);
string SqlList = DBHelper.GetSqlPage(SqlTable, SqlField, SqlParam, SqlOrder, CurrentPage, PageSize);
StringBuilder jsonBuilder = new StringBuilder();
jsonBuilder.Append("{\"page\":" + CurrentPage + ",\"total\":" + PageCount + ",\"records\":" + RecordCount + ",\"rows\"");
jsonBuilder.Append(":[");
RdrList = DBHelper.ExecuteReader(SqlList);
while (RdrList.Read())
{
Supplier_ID = tools.NullInt(RdrList["Supplier_ID"]);
Supplier_Name = tools.NullStr(RdrList["Supplier_Name"]);
Supplier_Address = tools.NullStr(RdrList["Supplier_Address"]);
Supplier_Tel = tools.NullStr(RdrList["Supplier_Tel"]);
Supplier_Contact = tools.NullStr(RdrList["Supplier_Contact"]);
jsonBuilder.Append("{\"Supplier_ID\":" + Supplier_ID + ",\"cell\":[");
//各字段
jsonBuilder.Append("\"");
jsonBuilder.Append(Supplier_ID);
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Supplier_Name));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Supplier_Address));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Supplier_Tel));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
jsonBuilder.Append(Public.JsonStr(Supplier_Contact));
jsonBuilder.Append("\",");
jsonBuilder.Append("\"");
if (Public.CheckPrivilege("3306c908-ff91-4e6e-8c46-8157cd5b6e4a"))
{
jsonBuilder.Append("<img src=\\\"/images/icon_edit.gif\\\" alt=\\\"修改\\\"> <a href=\\\"supplier_add.aspx?action=renew&supplier_id=" + Supplier_ID + "\\\" title=\\\"修改\\\">修改</a>");
}
if (Public.CheckPrivilege("21de0894-85a2-4719-8088-774f1a815f43"))
{
jsonBuilder.Append(" <img src=\\\"/images/icon_del.gif\\\" alt=\\\"删除\\\"> <a href=\\\"javascript:void(0);\\\" onclick=\\\"confirmdelete('supplier_do.aspx?action=move&supplier_id=" + Supplier_ID + "')\\\" title=\\\"删除\\\">删除</a>");
}
jsonBuilder.Append("\"");
jsonBuilder.Append("]},");
}
jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
jsonBuilder.Append("]");
jsonBuilder.Append("}");
return(jsonBuilder.ToString());
}
catch (Exception ex) {
throw ex;
return("");
}
finally {
if (RdrList != null)
{
RdrList.Close();
RdrList = null;
}
}
}