public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/json";
string sql = "";
string methodName = context.Request.QueryString["method"];
System.IO.StreamReader reader = new System.IO.StreamReader(context.Request.InputStream);
string value = reader.ReadToEnd();
DataAccessS1 dac = new DataAccessS1();
SqlDataReader dr;
System.Web.Script.Serialization.JavaScriptSerializer oSerializer1;
oSerializer1 = new System.Web.Script.Serialization.JavaScriptSerializer();
//dynamic data;
DataManagerS1 odem = new DataManagerS1();
string sJSON = "", uid = "";
switch (methodName)
{
case "login":
var ss = oSerializer1.Deserialize <dynamic>(value);
string email = ss["uid"];
string pwd = ss["pwd"];
string devInfo = context.Request.QueryString["devInfo"];
sJSON = "";
// sql = "select (cast(ID as varchar(50))+'~'+UType+'~'+Uname) [role] from dbo.Users where email ='" + email + "' and pwd='" + pwd + "' and isdeleted=0";
odem.Add("@email", email);
odem.Add("@pwd", pwd);
sJSON = Convert.ToString(odem.ExecuteScalar("LoginSP"));
if (!string.IsNullOrEmpty(sJSON))
{
//sJSON = "{\"uid\":\"" + sJSON.Split('~')[0] + "\",\"role\":\"" + sJSON.Split('~')[1].ToLower() + "\",\"name\":\"" + sJSON.Split('~')[2].ToLower() + "\"}";
sJSON = "{\"uid\":\"" + sJSON.Split('~')[0] + "\",\"role\":\"" + sJSON.Split('~')[1].ToLower() + "\",\"name\":\"" + sJSON.Split('~')[2].ToLower() + "\",\"email\":\"" + sJSON.Split('~')[3] + "\",\"mob\":\"" + sJSON.Split('~')[4] + "\"}";
}
//context.Response.ContentType = "application/text";
break;
case "register":
ss = oSerializer1.Deserialize <dynamic>(value);
odem = new DataManagerS1();
odem.Add("@UName", ss["name"]);
odem.Add("@Address", ss["add"]);
odem.Add("@Email", ss["uid"]);
odem.Add("@Pwd", ss["pwd"]);
odem.Add("@Mobile", ss["mob"]);
odem.Add("@UType", ss["utype"]);
odem.Add("@CreatedBy", 0);
try
{
if (odem.ExecuteNonQuery("RegisterSP") > 0)
{
sJSON = "1";
}
}
catch
{
sJSON = "";
}
//context.Response.ContentType = "application/text";
break;
case "registerSE":
ss = oSerializer1.Deserialize <dynamic>(value);
odem = new DataManagerS1();
odem.Add("@UName", ss["name"]);
odem.Add("@Address", ss["add"]);
odem.Add("@Email", ss["uid"]);
odem.Add("@Pwd", ss["pwd"]);
odem.Add("@Mobile", ss["mob"]);
odem.Add("@UType", "s");
odem.Add("@CreatedBy", 0);
try
{
if (odem.ExecuteNonQuery("RegisterSP") > 0)
{
sJSON = "1";
}
}
catch
{
sJSON = "";
}
//context.Response.ContentType = "application/text";
break;
case "Categories":
dac = new DataAccessS1();
sql = "select ID,Cname,CImg from CategoryMaster where IsDeleted=0";
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Category> lstCategory = new List <Category>();
Category objCategory;
while (dr.Read())
{
objCategory = new Category();
objCategory.ID = dr["ID"].ToString();
objCategory.Cname = dr["Cname"].ToString();
objCategory.CImg = dr["CImg"].ToString();
lstCategory.Add(objCategory);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstCategory);
break;
case "SubCategories":
SqlDataReader sqlDataReader5 = new DataAccessS1().ExecuteReader("select ID,SubCatName,Simg from SubCatMaster where IsDeleted=0 and CategoryMasterID=" + context.Request.QueryString["catid"], CommandType.Text);
List <Category> categoryList2 = new List <Category>();
while (sqlDataReader5.Read())
{
categoryList2.Add(new Category()
{
ID = sqlDataReader5["ID"].ToString(),
Cname = sqlDataReader5["SubCatName"].ToString(),
CImg = sqlDataReader5["Simg"].ToString()
});
}
sqlDataReader5.Close();
sJSON = new JavaScriptSerializer().Serialize((object)categoryList2);
break;
case "Productsbkp":
string catid = context.Request.QueryString["catid"];
dac = new DataAccessS1();
sql = "select * from Products where IsDeleted=0 and CategoryMasterID=" + catid;
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Product> lstProduct = new List <Product>();
Product objProduct;
while (dr.Read())
{
objProduct = new Product();
objProduct.ID = dr["ID"].ToString();
objProduct.Pname = dr["Pname"].ToString();
objProduct.Pimg = dr["Pimg"].ToString();
objProduct.PDesc = dr["PDesc"].ToString();
objProduct.PShortDesc = dr["PShortDesc"].ToString();
objProduct.Price = dr["Price"].ToString();
lstProduct.Add(objProduct);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstProduct);
break;
case "Products":
if (context.Request.QueryString["scatid"] == null)
{
sql = "select * from Products where IsDeleted=0";
}
else
{
sql = "select * from Products where IsDeleted=0 and SubCategoryMasterID=" + context.Request.QueryString["scatid"];
}
SqlDataReader sqlDataReader3 = new DataAccessS1().ExecuteReader(sql, CommandType.Text);
List <Product> productList1 = new List <Product>();
while (sqlDataReader3.Read())
{
productList1.Add(new Product()
{
ID = sqlDataReader3["ID"].ToString(),
Pname = sqlDataReader3["Pname"].ToString(),
Pimg = sqlDataReader3["Pimg"].ToString(),
PDesc = sqlDataReader3["PDesc"].ToString(),
PShortDesc = sqlDataReader3["PShortDesc"].ToString(),
Price = sqlDataReader3["Price"].ToString()
});
}
sqlDataReader3.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList1);
break;
case "addtocart":
uid = context.Request.QueryString["uid"];
string pid = context.Request.QueryString["pid"];
dac = new DataAccessS1();
dac.addParam("@custid", uid);
dac.addParam("@productid", pid);
if (dac.ExecuteQuerySP("spAddToCart") > 0)
{
sJSON = "1";
}
//string AmcID = context.Request.QueryString["AMCID"];
//string amcdetailid = context.Request.QueryString["AMCDetailID"];
//string Problem = context.Request.QueryString["Problem"];
break;
case "viewcart":
uid = context.Request.QueryString["uid"];
dac = new DataAccessS1();
sql = @"select C.ID,p.Pname,(p.Price*C.Qty) [Price],P.Pimg,C.Qty,P.PShortDesc,P.ID [PID] from dbo.Products P join dbo.CartDetails C on P.ID=C.ProductID
where P.IsDeleted=0 and C.CustID=" + uid;
dr = dac.ExecuteReader(sql, CommandType.Text);
lstProduct = new List <Product>();
//Product objProduct;
while (dr.Read())
{
objProduct = new Product();
objProduct.ID = dr["ID"].ToString();
objProduct.Pname = dr["Pname"].ToString();
objProduct.Pimg = dr["Pimg"].ToString();
objProduct.PShortDesc = dr["PShortDesc"].ToString();
objProduct.Price = dr["Price"].ToString();
objProduct.Qty = dr["Qty"].ToString();
objProduct.PID = dr["PID"].ToString();
lstProduct.Add(objProduct);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstProduct);
break;
case "removefromcart":
uid = context.Request.QueryString["uid"];
string cartid = context.Request.QueryString["cartid"];
sql = "delete from dbo.CartDetails where Id=" + cartid;
if (dac.executeTQuery(sql) > 0)
{
sJSON = "1";
}
break;
case "getproducts":
uid = context.Request.QueryString["uid"];
pid = context.Request.QueryString["pid"];
dac = new DataAccessS1();
if (pid == "0")
{
sql = @"select C.ID,p.Pname,(p.Price*C.Qty) [Price],P.Pimg,C.Qty,P.PShortDesc,P.ID [PID] from dbo.Products P join dbo.CartDetails C on P.ID=C.ProductID
where P.IsDeleted=0 and C.CustID=" + uid;
}
else
{
sql = "select p.ID,p.Pname, [Price],P.Pimg,1 [Qty],P.PShortDesc,P.ID [PID] from dbo.Products p where ID=" + pid;
}
dr = dac.ExecuteReader(sql, CommandType.Text);
lstProduct = new List <Product>();
//Product objProduct;
while (dr.Read())
{
objProduct = new Product();
objProduct.ID = dr["ID"].ToString();
objProduct.Pname = dr["Pname"].ToString();
objProduct.Pimg = dr["Pimg"].ToString();
objProduct.PShortDesc = dr["PShortDesc"].ToString();
objProduct.Price = dr["Price"].ToString();
objProduct.Qty = dr["Qty"].ToString();
objProduct.PID = dr["PID"].ToString();
lstProduct.Add(objProduct);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstProduct);
break;
case "Order":
string str3 = context.Request.QueryString["uid"];
string str4 = context.Request.QueryString["pid"];
string str5 = context.Request.QueryString["payAmt"];
string str6 = context.Request.QueryString["ActualAmt"];
string str7 = context.Request.QueryString["AdvAmt"];
string str8 = context.Request.QueryString["PaymentMode"];
string str9 = context.Request.QueryString["AdvType_Half_Full"];
string str10 = context.Request.QueryString["Qty"];
string str11 = context.Request.QueryString["paymentID"];
string NameSP = "SaveOrder";
DataAccessS1 dataAccessS1_2 = new DataAccessS1();
dataAccessS1_2.addParam("@uid", (object)str3);
dataAccessS1_2.addParam("@pid", (object)str4);
dataAccessS1_2.addParam("@ActualAmt", (object)str6);
dataAccessS1_2.addParam("@AdvAmt", (object)str7);
dataAccessS1_2.addParam("@PaymentMode", (object)str8);
dataAccessS1_2.addParam("@AdvType_Half_Full", (object)str9);
dataAccessS1_2.addParam("@Qty", (object)str10);
dataAccessS1_2.addParam("@paymentID", (object)str11);
dataAccessS1_2.addParam("@Address", context.Request.QueryString["address"]);
dataAccessS1_2.addParam("@City", context.Request.QueryString["city"]);
dataAccessS1_2.addParam("@Pin", context.Request.QueryString["pin"]);
dataAccessS1_2.addParam("@LandMark", context.Request.QueryString["landmark"]);
sJSON = "";
if (dataAccessS1_2.ExecuteQuerySP(NameSP) > 0)
{
sJSON = "1";
break;
}
break;
case "vieworder":
SqlDataReader sqlDataReader10 = new DataAccessS1().ExecuteReader("select OD.ID,p.Pname,(OD.Price*OD.Qty) [Price],P.Pimg,OD.Qty,P.PShortDesc,P.ID [PID],OD.sts \r\n from dbo.Products P join dbo.OrderDetails OD on P.ID=OD.ProductID \r\n join dbo.OrderMaster OM on OD.OrderMasterID=OM.ID\r\n where P.IsDeleted=0 and OM.CustID=" + context.Request.QueryString["uid"], CommandType.Text);
List <Product> productList4 = new List <Product>();
while (sqlDataReader10.Read())
{
productList4.Add(new Product()
{
ID = sqlDataReader10["ID"].ToString(),
Pname = sqlDataReader10["Pname"].ToString(),
Pimg = sqlDataReader10["Pimg"].ToString(),
PShortDesc = sqlDataReader10["PShortDesc"].ToString(),
Price = sqlDataReader10["Price"].ToString(),
Qty = sqlDataReader10["Qty"].ToString(),
PID = sqlDataReader10["PID"].ToString(),
OrderSts = sqlDataReader10["sts"].ToString()
});
}
sqlDataReader10.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList4);
break;
case "NewOrderAdmin":
sql = @"SELECT O.[ID]
,[CustID]
,u.UName
,[OrderValue]
,convert(varchar(10),[Date],103) [Date]
,[Remarks]
,[OrderNo]
,[PayMode]
,[AdvAmt]
,[AdvType_Half_Full]
,[PaymentID]
,O.[Address]
,O.[City]
,O.[Pin]
,O.[LandMark],Mobile
FROM [dbo].[OrderMaster] O join Users u on U.ID=O.CustID where O.Sts='OrderReceived'";
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Order> lstOrders = new List <Order>();
Order objOrder;
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.CustID = dr["CustID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.OrderValue = dr["OrderValue"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Remarks = dr["Remarks"].ToString();
objOrder.OrderNo = dr["OrderNo"].ToString();
objOrder.PayMode = dr["PayMode"].ToString();
objOrder.AdvAmt = dr["AdvAmt"].ToString();
objOrder.AdvType_Half_Full = dr["AdvType_Half_Full"].ToString();
objOrder.PaymentID = dr["PaymentID"].ToString();
objOrder.Address = dr["Address"].ToString();
objOrder.City = dr["City"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.LandMark = dr["LandMark"].ToString();
objOrder.Mobile = dr["Mobile"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "AssignedOrderAdmin":
sql = @"SELECT O.[ID]
,[CustID]
,u.UName
,[OrderValue]
,convert(varchar(10),[Date],103) [Date]
,[Remarks]
,[OrderNo]
,[PayMode]
,[AdvAmt]
,[AdvType_Half_Full]
,[PaymentID]
,O.[Address]
,O.[City]
,O.[Pin]
,O.[LandMark],A.[SE_Names],CONVERT(varchar(10),A.[FromDate],103) [From]
,CONVERT(varchar(10),A.[ToDate],103) [To]
FROM [dbo].[OrderMaster] O join Users u on U.ID=O.CustID
join AssignmentMaster A on A.OrderMasterID=O.ID
where O.Sts='Assigned' order by [FromDate] desc" ;
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
lstOrders = new List <Order>();
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.CustID = dr["CustID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.OrderValue = dr["OrderValue"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Remarks = dr["Remarks"].ToString();
objOrder.OrderNo = dr["OrderNo"].ToString();
objOrder.PayMode = dr["PayMode"].ToString();
objOrder.AdvAmt = dr["AdvAmt"].ToString();
objOrder.AdvType_Half_Full = dr["AdvType_Half_Full"].ToString();
objOrder.PaymentID = dr["PaymentID"].ToString();
objOrder.Address = dr["Address"].ToString();
objOrder.City = dr["City"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.LandMark = dr["LandMark"].ToString();
objOrder.SE_Names = dr["SE_Names"].ToString();
objOrder.From = dr["From"].ToString();
objOrder.To = dr["To"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "sehome":
uid = context.Request.QueryString["uid"];
sql = @"GetSEwiseJobs";
dac = new DataAccessS1();
dac.addParam("@seid", uid);
dr = dac.ExecuteReader(sql, CommandType.StoredProcedure);
lstOrders = new List <Order>();
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.CustID = dr["CustID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.OrderValue = dr["OrderValue"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Remarks = dr["Remarks"].ToString();
objOrder.OrderNo = dr["OrderNo"].ToString();
objOrder.PayMode = dr["PayMode"].ToString();
objOrder.AdvAmt = dr["AdvAmt"].ToString();
objOrder.AdvType_Half_Full = dr["AdvType_Half_Full"].ToString();
objOrder.PaymentID = dr["PaymentID"].ToString();
objOrder.Address = dr["Address"].ToString();
objOrder.City = dr["City"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.LandMark = dr["LandMark"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "vieworderAdmin":
dr = new DataAccessS1().ExecuteReader(@"select OM.ID,p.Pname,(OD.Price*OD.Qty) [Price],P.Pimg,OD.Qty,P.PShortDesc,P.ID [PID],OD.sts
from dbo.Products P join dbo.OrderDetails OD on P.ID=OD.ProductID join dbo.OrderMaster OM
on OD.OrderMasterID=OM.ID where P.IsDeleted=0 and OM.ID=" + context.Request.QueryString["id"], CommandType.Text);
productList4 = new List <Product>();
while (dr.Read())
{
productList4.Add(new Product()
{
ID = dr["ID"].ToString(),
Pname = dr["Pname"].ToString(),
Pimg = dr["Pimg"].ToString(),
PShortDesc = dr["PShortDesc"].ToString(),
Price = dr["Price"].ToString(),
Qty = dr["Qty"].ToString(),
PID = dr["PID"].ToString(),
OrderSts = dr["sts"].ToString()
});
}
dr.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList4);
break;
case "GetSEForAssignmentDatewise":
//string fromdate = context.Request.QueryString["fromdate"].ToString().Replace("-", "");
//string todate = context.Request.QueryString["todate"].ToString().Replace("-", "");
//sql = "GetSEforOrderAssignment";
sql = " select U.ID,U.UName from users u where U.UType='v'";
dac = new DataAccessS1();
//dac.addParam("@date", fromdate);
// dac.addParam("@enddate", todate);
dr = dac.ExecuteReader(sql, CommandType.Text);
List <SEList> lstSEList = new List <SEList>();
SEList objSEList;
while (dr.Read())
{
objSEList = new SEList();
objSEList.ID = dr["ID"].ToString();
objSEList.UName = dr["UName"].ToString();
objSEList.ischecked = false;
lstSEList.Add(objSEList);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstSEList);
break;
case "AssignOrder":
string fromdate = context.Request.QueryString["fromdate"].ToString().Replace("-", "");
// todate = context.Request.QueryString["todate"].ToString().Replace("-", "");
string IDS = context.Request.QueryString["IDS"].ToString();
dac = new DataAccessS1();
dac.addParam("@fromdate", fromdate);
dac.addParam("@enddate", fromdate);
dac.addParam("@se_ids", IDS);
dac.addParam("@OrderMasterID", context.Request.QueryString["ID"].ToString());
dac.addParam("@AssignedBy", context.Request.QueryString["uid"].ToString());
if (dac.ExecuteQuerySP("SaveAssignOrder") > 0)
{
sJSON = "1";
}
break;
case "otpgen":
string orderid = context.Request.QueryString["orderid"];
dac = new DataAccessS1();
dac.addParam("@orderid", orderid);
dr = dac.ExecuteReader("OTPgeneration", CommandType.StoredProcedure);
string otp = "", no = "";
while (dr.Read())
{
otp = dr["OTP"].ToString();
no = dr["Mobile"].ToString();
}
dr.Close();
Api.Classes.Mailer.SendSMS(no, "withU service completion OTP : " + otp);
sJSON = "1";
break;
case "updateorder":
orderid = context.Request.QueryString["orderid"];
otp = context.Request.QueryString["otp"];
uid = context.Request.QueryString["uid"];
string rem = context.Request.QueryString["rem"];
string sts = context.Request.QueryString["sts"];
dac = new DataAccessS1();
dac.addParam("@orderid", orderid);
dac.addParam("@uid", uid);
dac.addParam("@rem", rem);
dac.addParam("@otp", otp);
dac.addParam("@sts", sts);
dr = dac.ExecuteReader("SpUpdateOrderSE", CommandType.StoredProcedure);
sJSON = "0";
while (dr.Read())
{
sJSON = dr["result"].ToString();
//no = dr["Mobile"].ToString();
}
dr.Close();
//sJSON = "1";
break;
case "pendingverify":
sql = @"select ID,Uname,Address,dist,[state],Mobile,Pin,occupation from users
where UType='c' and IsDeleted=0 and IsVerified=0 and
dist=(select dist from users where id=" + context.Request.QueryString["uid"] + ")";
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Consumers> lstCons = new List <Consumers>();
Consumers objCon;
sJSON = "0";
while (dr.Read())
{
objCon = new Consumers();
objCon.ID = dr["ID"].ToString();
objCon.UName = dr["Uname"].ToString();
objCon.Address = dr["Address"].ToString();
objCon.Dist = dr["dist"].ToString();
objCon.State = dr["state"].ToString();
objCon.Pin = dr["Pin"].ToString();
objCon.Mobile = dr["Mobile"].ToString();
objCon.occupation = dr["occupation"].ToString();
lstCons.Add(objCon);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstCons);
break;
case "approve_reject":
string id = context.Request.QueryString["id"];
string action = context.Request.QueryString["action"];
if (action == "a")
{
sql = "update users set IsVerified=1 where Id=" + id;
}
else
{
sql = "update users set IsDeleted=1 where Id=" + id;
}
if (dac.executeTQuery(sql) > 0)
{
sJSON = "1";
}
break;
case "OrderCompletionByVol":
sql = @"select O.ID,Uname,convert(varchar(10),O.Date,103) [Date],O.Sts,U.Dist,U.Address,U.Pin
from OrderMaster O join Users U on O.custid=U.ID where VolunteerID=" + context.Request.QueryString["uid"];
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
lstOrders = new List <Order>();
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.City = dr["dist"].ToString();
objOrder.Address = dr["Address"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "OrderHistoryByDonor":
uid = context.Request.QueryString["uid"];
sql = @"select O.ID,U.UName,CONVERT(varchar(10),O.Date,103) [Date],O.Sts from orderMaster O join [AssignmentMaster] A on A.OrderMasterID=O.ID
join Users U on O.CustID=U.ID where SE_Names is not null and AssignedBy =" + uid;
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
lstOrders = new List <Order>();
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Sts = dr["Sts"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "ProductRecom":
uid = context.Request.QueryString["uid"];
string[] product_cols = { "CUSTID", "Detergent Powder", "Dettol", "Geometry Sets", "Handwash", "Note Book", "Pen", "Pencil", "Rechargeble Solor Light", "Regular Rice 10 kg", "Solar Cooker", "Glucose solution", "Cerelac From 6 Months", "Cerelac From 12 Months", "Rice 5 kg", "Milk Cereal", "OZiva Protein", "Veg Soup", "Horlicks" };
DataTable df = ConvertCSVtoDataTable(context.Server.MapPath("~/s1/customers_orders1_opt_updated.csv"));
//DataTable df_filtered = df.Select(["CUST_ID"] + product_cols);
System.Data.DataView view = new System.Data.DataView(df);
System.Data.DataTable df_filtered =
view.ToTable("Selected", false, product_cols);
DataTable df_customer_products = df_filtered.AsEnumerable()
.GroupBy(r => r.Field <string>("CUSTID"))
.Select(g =>
{
var row = df_filtered.NewRow();
row["CUSTID"] = g.Key;
row["Detergent Powder"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Detergent Powder")));
row["Dettol"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Dettol")));
row["Geometry Sets"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Geometry Sets")));
row["Handwash"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Handwash")));
//row["Baby wash"] = g.Sum(r =>Convert.ToInt32( r.Field<string>("Baby wash")));
row["Note Book"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Note Book")));
row["Pen"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Pen")));
row["Pencil"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Pencil")));
row["Rechargeble Solor Light"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Rechargeble Solor Light")));
row["Regular Rice 10 kg"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Regular Rice 10 kg")));
row["Solar Cooker"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Solar Cooker")));
row["Glucose solution"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Glucose solution")));
row["Cerelac From 6 Months"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Cerelac From 6 Months")));
row["Cerelac From 12 Months"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Cerelac From 12 Months")));
row["Rice 5 kg"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Rice 5 kg")));
row["Milk Cereal"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Milk Cereal")));
row["Veg Soup"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Veg Soup")));
row["Horlicks"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Horlicks")));
return(row);
}).CopyToDataTable();
//use caching to avoid loop
DataTable df_customer_products_cluster = df_customer_products.Copy();
df_customer_products_cluster.Columns.Add("cluster", typeof(int));
df_customer_products_cluster.AcceptChanges();
foreach (DataRow r in df_customer_products_cluster.Rows)
{
r["cluster"] = new Random().Next(10, 99);
}
dac = new DataAccessS1();
sql = @"select p.Pname,C.Qty from dbo.Products P join dbo.CartDetails C on P.ID=C.ProductID
where P.IsDeleted=0 and C.CustID=" + uid;
dr = dac.ExecuteReader(sql, CommandType.Text);
lstProduct = new List <Product>();
//Product objProduct;
string fields = "", values = "";
string cols = "";
int cnt = 0;
while (dr.Read())
{
fields = fields + "\"sum(" + dr["Pname"].ToString() + ")\",";
cols += dr["Pname"].ToString() + ",";
values += Convert.ToInt16(dr["Qty"].ToString()).ToString() + ",";
cnt++;
}
dr.Close();
if (cols == "")
{
cols = "Dettol,";
}
foreach (string col in product_cols)
{
if (!fields.Contains(col))
{
fields = fields + "\"sum(" + col + ")\",";
values += "0,";
cnt++;
}
}
fields = fields.Remove(fields.Length - 1, 1);
values = values.Remove(values.Length - 1, 1);
//string scoring_payload = "{\"fields\":[\"sum(Baby Food)\",\"sum(Diapers)\",\"sum(Formula)\",\"sum(Lotion)\",\"sum(Baby wash)\",\"sum(Wipes)\",\"sum(Fresh Fruits)\",\"sum(Fresh Vegetables)\",\"sum(Beer)\",\"sum(Wine)\",\"sum(Club Soda)\",\"sum(Sports Drink)\",\"sum(Chips)\",\"sum(Popcorn)\",\"sum(Oatmeal)\",\"sum(Medicines)\",\"sum(Canned Foods)\",\"sum(Cigarettes)\",\"sum(Cheese)\",\"sum(Cleaning Products)\",\"sum(Condiments)\",\"sum(Frozen Foods)\",\"sum(Kitchen Items)\",\"sum(Meat)\",\"sum(Office Supplies)\",\"sum(Personal Care)\",\"sum(Pet Supplies)\",\"sum(Sea Food)\",\"sum(Spices)\"],\"values\":[[1,0,0,0,0,0,0,0,0,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]]}";
string scoring_payload = "{\"fields\":[" + fields + "],\"values\":[[" + values + "]]}";
ServicePointManager.Expect100Continue = true;
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
var request = (HttpWebRequest)WebRequest.Create("https://iam.bluemix.net/identity/token");
var postData = "apikey=CFdFMUPI3YBwFUMPcjVSXUmNvnJp1OEiNDzT0VG79yX2&grant_type=urn:ibm:params:oauth:grant-type:apikey";
var data = Encoding.ASCII.GetBytes(postData);
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
request.ContentLength = data.Length;
using (var stream = request.GetRequestStream())
{
stream.Write(data, 0, data.Length);
}
var response = (HttpWebResponse)request.GetResponse();
var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
var json = oSerializer1.Deserialize <dynamic>(responseString);
string access_token = json["access_token"];
request = (HttpWebRequest)WebRequest.Create("https://eu-gb.ml.cloud.ibm.com/v3/wml_instances/da7bd924-fe24-41c2-a6d9-a5259a586ad5/deployments/c15fb9d5-78cc-44db-9119-8e8981d6b1b6/online");
data = Encoding.ASCII.GetBytes(scoring_payload);
request.Method = "POST";
request.ContentType = "application/json";
request.Headers.Add("Authorization", "Bearer " + access_token);
request.Headers.Add("ML-Instance-ID", "da7bd924-fe24-41c2-a6d9-a5259a586ad5");
request.ContentLength = data.Length;
using (var stream = request.GetRequestStream())
{
stream.Write(data, 0, data.Length);
}
response = (HttpWebResponse)request.GetResponse();
responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
json = oSerializer1.Deserialize <dynamic>(responseString);
int cluster = (int)(json["values"][0][cnt + 1]);
DataRow[] df_cluster_products = df_customer_products_cluster.Select("cluster=" + cluster.ToString());
if (df_cluster_products.Length == 0)
{
df_cluster_products = df_customer_products_cluster.Select("" + product_cols[2] + ">0");
}
DataTable df_cluster_products_agg = df_cluster_products.AsEnumerable()
.GroupBy(r => r.Field <int>("cluster"))
.Select(g =>
{
var row = df_filtered.NewRow();
//row["cluster"] = g.Key;
row["Detergent Powder"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Detergent Powder")));
row["Dettol"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Dettol")));
row["Geometry Sets"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Geometry Sets")));
row["Handwash"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Handwash")));
row["Note Book"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Note Book")));
row["Pen"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Pen")));
row["Pencil"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Pencil")));
row["Rechargeble Solor Light"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Rechargeble Solor Light")));
row["Regular Rice 10 kg"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Regular Rice 10 kg")));
row["Solar Cooker"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Solar Cooker")));
row["Glucose solution"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Glucose solution")));
row["Cerelac From 6 Months"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Cerelac From 6 Months")));
row["Cerelac From 12 Months"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Cerelac From 12 Months")));
row["Rice 5 kg"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Rice 5 kg")));
row["Milk Cereal"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Milk Cereal")));
row["Veg Soup"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Veg Soup")));
row["Horlicks"] = g.Sum(r => Convert.ToInt32(r.Field <string>("Horlicks")));
return(row);
}).CopyToDataTable();
DataRow row_ = df_cluster_products_agg.Rows[0];
Dictionary <string, int> products_acnt = new Dictionary <string, int>();
df_cluster_products_agg.Columns.Remove("CUSTID");
df_cluster_products_agg.AcceptChanges();
sqlDataReader3 = new DataAccessS1().ExecuteReader("select * from Products where IsDeleted=0", CommandType.Text);
productList1 = new List <Product>();
while (sqlDataReader3.Read())
{
foreach (DataColumn dc in df_cluster_products_agg.Columns)
{
if (sqlDataReader3["Pname"].ToString() == dc.ColumnName)
{
if (!string.IsNullOrEmpty(Convert.ToString(row_[dc.ColumnName])))
{
if (Convert.ToInt32(row_[dc.ColumnName].ToString()) > 0)
{
productList1.Add(new Product()
{
ID = sqlDataReader3["ID"].ToString(),
Pname = sqlDataReader3["Pname"].ToString(),
Pimg = sqlDataReader3["Pimg"].ToString(),
PDesc = sqlDataReader3["PDesc"].ToString(),
PShortDesc = sqlDataReader3["PShortDesc"].ToString(),
Price = sqlDataReader3["Price"].ToString()
});
}
}
}
}
}
sqlDataReader3.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList1);
break;
}
context.Response.Write(sJSON);
//context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/json";
string sql = "";
string methodName = context.Request.QueryString["method"];
System.IO.StreamReader reader = new System.IO.StreamReader(context.Request.InputStream);
string value = reader.ReadToEnd();
DataAccessS1 dac = new DataAccessS1();
SqlDataReader dr;
System.Web.Script.Serialization.JavaScriptSerializer oSerializer1;
oSerializer1 = new System.Web.Script.Serialization.JavaScriptSerializer();
//dynamic data;
DataManagerS1 odem = new DataManagerS1();
string sJSON = "", uid = "";
switch (methodName)
{
case "login":
var ss = oSerializer1.Deserialize <dynamic>(value);
string email = ss["uid"];
string pwd = ss["pwd"];
string devInfo = context.Request.QueryString["devInfo"];
sJSON = "";
// sql = "select (cast(ID as varchar(50))+'~'+UType+'~'+Uname) [role] from dbo.Users where email ='" + email + "' and pwd='" + pwd + "' and isdeleted=0";
odem.Add("@email", email);
odem.Add("@pwd", pwd);
sJSON = Convert.ToString(odem.ExecuteScalar("LoginSP"));
if (!string.IsNullOrEmpty(sJSON))
{
//sJSON = "{\"uid\":\"" + sJSON.Split('~')[0] + "\",\"role\":\"" + sJSON.Split('~')[1].ToLower() + "\",\"name\":\"" + sJSON.Split('~')[2].ToLower() + "\"}";
sJSON = "{\"uid\":\"" + sJSON.Split('~')[0] + "\",\"role\":\"" + sJSON.Split('~')[1].ToLower() + "\",\"name\":\"" + sJSON.Split('~')[2].ToLower() + "\",\"email\":\"" + sJSON.Split('~')[3] + "\",\"mob\":\"" + sJSON.Split('~')[4] + "\"}";
}
//context.Response.ContentType = "application/text";
break;
case "register":
ss = oSerializer1.Deserialize <dynamic>(value);
odem = new DataManagerS1();
odem.Add("@UName", ss["name"]);
odem.Add("@Address", ss["add"]);
odem.Add("@Email", ss["uid"]);
odem.Add("@Pwd", ss["pwd"]);
odem.Add("@Mobile", ss["mob"]);
odem.Add("@UType", "c");
odem.Add("@CreatedBy", 0);
try
{
if (odem.ExecuteNonQuery("RegisterSP") > 0)
{
sJSON = "1";
}
}
catch
{
sJSON = "";
}
//context.Response.ContentType = "application/text";
break;
case "registerSE":
ss = oSerializer1.Deserialize <dynamic>(value);
odem = new DataManagerS1();
odem.Add("@UName", ss["name"]);
odem.Add("@Address", ss["add"]);
odem.Add("@Email", ss["uid"]);
odem.Add("@Pwd", ss["pwd"]);
odem.Add("@Mobile", ss["mob"]);
odem.Add("@UType", "s");
odem.Add("@CreatedBy", 0);
try
{
if (odem.ExecuteNonQuery("RegisterSP") > 0)
{
sJSON = "1";
}
}
catch
{
sJSON = "";
}
//context.Response.ContentType = "application/text";
break;
case "Categories":
dac = new DataAccessS1();
sql = "select ID,Cname,CImg from CategoryMaster where IsDeleted=0";
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Category> lstCategory = new List <Category>();
Category objCategory;
while (dr.Read())
{
objCategory = new Category();
objCategory.ID = dr["ID"].ToString();
objCategory.Cname = dr["Cname"].ToString();
objCategory.CImg = dr["CImg"].ToString();
lstCategory.Add(objCategory);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstCategory);
break;
case "SubCategories":
SqlDataReader sqlDataReader5 = new DataAccessS1().ExecuteReader("select ID,SubCatName,Simg from SubCatMaster where IsDeleted=0 and CategoryMasterID=" + context.Request.QueryString["catid"], CommandType.Text);
List <Category> categoryList2 = new List <Category>();
while (sqlDataReader5.Read())
{
categoryList2.Add(new Category()
{
ID = sqlDataReader5["ID"].ToString(),
Cname = sqlDataReader5["SubCatName"].ToString(),
CImg = sqlDataReader5["Simg"].ToString()
});
}
sqlDataReader5.Close();
sJSON = new JavaScriptSerializer().Serialize((object)categoryList2);
break;
case "Productsbkp":
string catid = context.Request.QueryString["catid"];
dac = new DataAccessS1();
sql = "select * from Products where IsDeleted=0 and CategoryMasterID=" + catid;
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Product> lstProduct = new List <Product>();
Product objProduct;
while (dr.Read())
{
objProduct = new Product();
objProduct.ID = dr["ID"].ToString();
objProduct.Pname = dr["Pname"].ToString();
objProduct.Pimg = dr["Pimg"].ToString();
objProduct.PDesc = dr["PDesc"].ToString();
objProduct.PShortDesc = dr["PShortDesc"].ToString();
objProduct.Price = dr["Price"].ToString();
lstProduct.Add(objProduct);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstProduct);
break;
case "Products":
SqlDataReader sqlDataReader3 = new DataAccessS1().ExecuteReader("select * from Products where IsDeleted=0 and SubCategoryMasterID=" + context.Request.QueryString["scatid"], CommandType.Text);
List <Product> productList1 = new List <Product>();
while (sqlDataReader3.Read())
{
productList1.Add(new Product()
{
ID = sqlDataReader3["ID"].ToString(),
Pname = sqlDataReader3["Pname"].ToString(),
Pimg = sqlDataReader3["Pimg"].ToString(),
PDesc = sqlDataReader3["PDesc"].ToString(),
PShortDesc = sqlDataReader3["PShortDesc"].ToString(),
Price = sqlDataReader3["Price"].ToString()
});
}
sqlDataReader3.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList1);
break;
case "addtocart":
uid = context.Request.QueryString["uid"];
string pid = context.Request.QueryString["pid"];
dac = new DataAccessS1();
dac.addParam("@custid", uid);
dac.addParam("@productid", pid);
if (dac.ExecuteQuerySP("spAddToCart") > 0)
{
sJSON = "1";
}
//string AmcID = context.Request.QueryString["AMCID"];
//string amcdetailid = context.Request.QueryString["AMCDetailID"];
//string Problem = context.Request.QueryString["Problem"];
break;
case "viewcart":
uid = context.Request.QueryString["uid"];
dac = new DataAccessS1();
sql = @"select C.ID,p.Pname,(p.Price*C.Qty) [Price],P.Pimg,C.Qty,P.PShortDesc,P.ID [PID] from dbo.Products P join dbo.CartDetails C on P.ID=C.ProductID
where P.IsDeleted=0 and C.CustID=" + uid;
dr = dac.ExecuteReader(sql, CommandType.Text);
lstProduct = new List <Product>();
//Product objProduct;
while (dr.Read())
{
objProduct = new Product();
objProduct.ID = dr["ID"].ToString();
objProduct.Pname = dr["Pname"].ToString();
objProduct.Pimg = dr["Pimg"].ToString();
objProduct.PShortDesc = dr["PShortDesc"].ToString();
objProduct.Price = dr["Price"].ToString();
objProduct.Qty = dr["Qty"].ToString();
objProduct.PID = dr["PID"].ToString();
lstProduct.Add(objProduct);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstProduct);
break;
case "removefromcart":
uid = context.Request.QueryString["uid"];
string cartid = context.Request.QueryString["cartid"];
sql = "delete from dbo.CartDetails where Id=" + cartid;
if (dac.executeTQuery(sql) > 0)
{
sJSON = "1";
}
break;
case "getproducts":
uid = context.Request.QueryString["uid"];
pid = context.Request.QueryString["pid"];
dac = new DataAccessS1();
if (pid == "0")
{
sql = @"select C.ID,p.Pname,(p.Price*C.Qty) [Price],P.Pimg,C.Qty,P.PShortDesc,P.ID [PID] from dbo.Products P join dbo.CartDetails C on P.ID=C.ProductID
where P.IsDeleted=0 and C.CustID=" + uid;
}
else
{
sql = "select p.ID,p.Pname, [Price],P.Pimg,1 [Qty],P.PShortDesc,P.ID [PID] from dbo.Products p where ID=" + pid;
}
dr = dac.ExecuteReader(sql, CommandType.Text);
lstProduct = new List <Product>();
//Product objProduct;
while (dr.Read())
{
objProduct = new Product();
objProduct.ID = dr["ID"].ToString();
objProduct.Pname = dr["Pname"].ToString();
objProduct.Pimg = dr["Pimg"].ToString();
objProduct.PShortDesc = dr["PShortDesc"].ToString();
objProduct.Price = dr["Price"].ToString();
objProduct.Qty = dr["Qty"].ToString();
objProduct.PID = dr["PID"].ToString();
lstProduct.Add(objProduct);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstProduct);
break;
case "Order":
string str3 = context.Request.QueryString["uid"];
string str4 = context.Request.QueryString["pid"];
string str5 = context.Request.QueryString["payAmt"];
string str6 = context.Request.QueryString["ActualAmt"];
string str7 = context.Request.QueryString["AdvAmt"];
string str8 = context.Request.QueryString["PaymentMode"];
string str9 = context.Request.QueryString["AdvType_Half_Full"];
string str10 = context.Request.QueryString["Qty"];
string str11 = context.Request.QueryString["paymentID"];
string NameSP = "SaveOrder";
DataAccessS1 dataAccessS1_2 = new DataAccessS1();
dataAccessS1_2.addParam("@uid", (object)str3);
dataAccessS1_2.addParam("@pid", (object)str4);
dataAccessS1_2.addParam("@ActualAmt", (object)str6);
dataAccessS1_2.addParam("@AdvAmt", (object)str7);
dataAccessS1_2.addParam("@PaymentMode", (object)str8);
dataAccessS1_2.addParam("@AdvType_Half_Full", (object)str9);
dataAccessS1_2.addParam("@Qty", (object)str10);
dataAccessS1_2.addParam("@paymentID", (object)str11);
dataAccessS1_2.addParam("@Address", context.Request.QueryString["address"]);
dataAccessS1_2.addParam("@City", context.Request.QueryString["city"]);
dataAccessS1_2.addParam("@Pin", context.Request.QueryString["pin"]);
dataAccessS1_2.addParam("@LandMark", context.Request.QueryString["landmark"]);
sJSON = "";
if (dataAccessS1_2.ExecuteQuerySP(NameSP) > 0)
{
sJSON = "1";
break;
}
break;
case "vieworder":
SqlDataReader sqlDataReader10 = new DataAccessS1().ExecuteReader("select OD.ID,p.Pname,(OD.Price*OD.Qty) [Price],P.Pimg,OD.Qty,P.PShortDesc,P.ID [PID],OD.sts \r\n from dbo.Products P join dbo.OrderDetails OD on P.ID=OD.ProductID \r\n join dbo.OrderMaster OM on OD.OrderMasterID=OM.ID\r\n where P.IsDeleted=0 and OM.CustID=" + context.Request.QueryString["uid"], CommandType.Text);
List <Product> productList4 = new List <Product>();
while (sqlDataReader10.Read())
{
productList4.Add(new Product()
{
ID = sqlDataReader10["ID"].ToString(),
Pname = sqlDataReader10["Pname"].ToString(),
Pimg = sqlDataReader10["Pimg"].ToString(),
PShortDesc = sqlDataReader10["PShortDesc"].ToString(),
Price = sqlDataReader10["Price"].ToString(),
Qty = sqlDataReader10["Qty"].ToString(),
PID = sqlDataReader10["PID"].ToString(),
OrderSts = sqlDataReader10["sts"].ToString()
});
}
sqlDataReader10.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList4);
break;
case "NewOrderAdmin":
sql = @"SELECT O.[ID]
,[CustID]
,u.UName
,[OrderValue]
,convert(varchar(10),[Date],103) [Date]
,[Remarks]
,[OrderNo]
,[PayMode]
,[AdvAmt]
,[AdvType_Half_Full]
,[PaymentID]
,O.[Address]
,[City]
,[Pin]
,[LandMark],Mobile
FROM [dbo].[OrderMaster] O join Users u on U.ID=O.CustID where O.Sts='OrderReceived'";
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
List <Order> lstOrders = new List <Order>();
Order objOrder;
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.CustID = dr["CustID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.OrderValue = dr["OrderValue"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Remarks = dr["Remarks"].ToString();
objOrder.OrderNo = dr["OrderNo"].ToString();
objOrder.PayMode = dr["PayMode"].ToString();
objOrder.AdvAmt = dr["AdvAmt"].ToString();
objOrder.AdvType_Half_Full = dr["AdvType_Half_Full"].ToString();
objOrder.PaymentID = dr["PaymentID"].ToString();
objOrder.Address = dr["Address"].ToString();
objOrder.City = dr["City"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.LandMark = dr["LandMark"].ToString();
objOrder.Mobile = dr["Mobile"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "AssignedOrderAdmin":
sql = @"SELECT O.[ID]
,[CustID]
,u.UName
,[OrderValue]
,convert(varchar(10),[Date],103) [Date]
,[Remarks]
,[OrderNo]
,[PayMode]
,[AdvAmt]
,[AdvType_Half_Full]
,[PaymentID]
,O.[Address]
,[City]
,[Pin]
,[LandMark],A.[SE_Names],CONVERT(varchar(10),A.[FromDate],103) [From]
,CONVERT(varchar(10),A.[ToDate],103) [To]
FROM [dbo].[OrderMaster] O join Users u on U.ID=O.CustID
join AssignmentMaster A on A.OrderMasterID=O.ID
where O.Sts='Assigned' order by [FromDate] desc" ;
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
lstOrders = new List <Order>();
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.CustID = dr["CustID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.OrderValue = dr["OrderValue"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Remarks = dr["Remarks"].ToString();
objOrder.OrderNo = dr["OrderNo"].ToString();
objOrder.PayMode = dr["PayMode"].ToString();
objOrder.AdvAmt = dr["AdvAmt"].ToString();
objOrder.AdvType_Half_Full = dr["AdvType_Half_Full"].ToString();
objOrder.PaymentID = dr["PaymentID"].ToString();
objOrder.Address = dr["Address"].ToString();
objOrder.City = dr["City"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.LandMark = dr["LandMark"].ToString();
objOrder.SE_Names = dr["SE_Names"].ToString();
objOrder.From = dr["From"].ToString();
objOrder.To = dr["To"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "sehome":
uid = context.Request.QueryString["uid"];
sql = @"GetSEwiseJobs";
dac = new DataAccessS1();
dac.addParam("@seid", uid);
dr = dac.ExecuteReader(sql, CommandType.StoredProcedure);
lstOrders = new List <Order>();
while (dr.Read())
{
objOrder = new Order();
objOrder.ID = dr["ID"].ToString();
objOrder.CustID = dr["CustID"].ToString();
objOrder.UName = dr["UName"].ToString();
objOrder.OrderValue = dr["OrderValue"].ToString();
objOrder.Date = dr["Date"].ToString();
objOrder.Remarks = dr["Remarks"].ToString();
objOrder.OrderNo = dr["OrderNo"].ToString();
objOrder.PayMode = dr["PayMode"].ToString();
objOrder.AdvAmt = dr["AdvAmt"].ToString();
objOrder.AdvType_Half_Full = dr["AdvType_Half_Full"].ToString();
objOrder.PaymentID = dr["PaymentID"].ToString();
objOrder.Address = dr["Address"].ToString();
objOrder.City = dr["City"].ToString();
objOrder.Pin = dr["Pin"].ToString();
objOrder.LandMark = dr["LandMark"].ToString();
lstOrders.Add(objOrder);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstOrders);
break;
case "vieworderAdmin":
dr = new DataAccessS1().ExecuteReader(@"select OM.ID,p.Pname,(OD.Price*OD.Qty) [Price],P.Pimg,OD.Qty,P.PShortDesc,P.ID [PID],OD.sts
from dbo.Products P join dbo.OrderDetails OD on P.ID=OD.ProductID join dbo.OrderMaster OM
on OD.OrderMasterID=OM.ID where P.IsDeleted=0 and OM.ID=" + context.Request.QueryString["id"], CommandType.Text);
productList4 = new List <Product>();
while (dr.Read())
{
productList4.Add(new Product()
{
ID = dr["ID"].ToString(),
Pname = dr["Pname"].ToString(),
Pimg = dr["Pimg"].ToString(),
PShortDesc = dr["PShortDesc"].ToString(),
Price = dr["Price"].ToString(),
Qty = dr["Qty"].ToString(),
PID = dr["PID"].ToString(),
OrderSts = dr["sts"].ToString()
});
}
dr.Close();
sJSON = new JavaScriptSerializer().Serialize((object)productList4);
break;
case "GetSEForAssignmentDatewise":
string fromdate = context.Request.QueryString["fromdate"].ToString().Replace("-", "");
string todate = context.Request.QueryString["todate"].ToString().Replace("-", "");
sql = "GetSEforOrderAssignment";
dac = new DataAccessS1();
dac.addParam("@date", fromdate);
dac.addParam("@enddate", todate);
dr = dac.ExecuteReader(sql, CommandType.StoredProcedure);
List <SEList> lstSEList = new List <SEList>();
SEList objSEList;
while (dr.Read())
{
objSEList = new SEList();
objSEList.ID = dr["ID"].ToString();
objSEList.UName = dr["UName"].ToString();
objSEList.ischecked = false;
lstSEList.Add(objSEList);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstSEList);
break;
case "AssignOrder":
fromdate = context.Request.QueryString["fromdate"].ToString().Replace("-", "");
todate = context.Request.QueryString["todate"].ToString().Replace("-", "");
string IDS = context.Request.QueryString["IDS"].ToString();
dac = new DataAccessS1();
dac.addParam("@fromdate", fromdate);
dac.addParam("@enddate", todate);
dac.addParam("@se_ids", IDS);
dac.addParam("@OrderMasterID", context.Request.QueryString["ID"].ToString());
dac.addParam("@AssignedBy", context.Request.QueryString["uid"].ToString());
if (dac.ExecuteQuerySP("SaveAssignOrder") > 0)
{
sJSON = "1";
}
break;
case "otpgen":
string orderid = context.Request.QueryString["orderid"];
dac = new DataAccessS1();
dac.addParam("@orderid", orderid);
dr = dac.ExecuteReader("OTPgeneration", CommandType.StoredProcedure);
string otp = "", no = "";
while (dr.Read())
{
otp = dr["OTP"].ToString();
no = dr["Mobile"].ToString();
}
dr.Close();
Api.Classes.Mailer.SendSMS(no, "Cleanmate service completion OTP : " + otp);
sJSON = "1";
break;
case "updateorder":
orderid = context.Request.QueryString["orderid"];
otp = context.Request.QueryString["otp"];
uid = context.Request.QueryString["uid"];
string rem = context.Request.QueryString["rem"];
string sts = context.Request.QueryString["sts"];
dac = new DataAccessS1();
dac.addParam("@orderid", orderid);
dac.addParam("@uid", uid);
dac.addParam("@rem", rem);
dac.addParam("@otp", otp);
dac.addParam("@sts", sts);
dr = dac.ExecuteReader("SpUpdateOrderSE", CommandType.StoredProcedure);
sJSON = "0";
while (dr.Read())
{
sJSON = dr["result"].ToString();
//no = dr["Mobile"].ToString();
}
dr.Close();
//sJSON = "1";
break;
case "PendingCallListSE":
uid = context.Request.QueryString["uid"];
sts = context.Request.QueryString["sts"];
sql = "GetAssignedCallSEStatuswise";
dac = new DataAccessS1();
dac.addParam("@SEID", uid);
dac.addParam("@Status", sts);
dr = dac.ExecuteReader(sql, CommandType.StoredProcedure);
List <CallList> lstCall = new List <CallList>();
CallList objCallList;
while (dr.Read())
{
objCallList = new CallList();
objCallList.ExpectedDate = dr["ExpectedDate"].ToString();
objCallList.Party = dr["Party"].ToString();
objCallList.Address = dr["Address"].ToString();
objCallList.Mobile1 = dr["Mobile1"].ToString();
objCallList.Product = dr["Product"].ToString();
objCallList.DistributionID = dr["DistributionID"].ToString();
objCallList.Problem = dr["Problem"].ToString();
objCallList.AMCDetailID = dr["AMCDetailID"].ToString();
lstCall.Add(objCallList);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstCall);
break;
case "ProductsPartyWise":
uid = context.Request.QueryString["uid"];
sql = "GetProductsPartyWise";
dac = new DataAccessS1();
dac.addParam("@PartyID", uid);
dr = dac.ExecuteReader(sql, CommandType.StoredProcedure);
lstCall = new List <CallList>();
while (dr.Read())
{
objCallList = new CallList();
//objCallList.Party = dr["Party"].ToString();
objCallList.Product = dr["Product"].ToString();
objCallList.EndDate = dr["EndDate"].ToString();
objCallList.ProductSlNo = dr["ProductSlNo"].ToString();
objCallList.AMCDetailID = dr["AMCDetailID"].ToString();
objCallList.AMCID = dr["AMCID"].ToString();
lstCall.Add(objCallList);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstCall);
break;
case "partycallbooking":
uid = context.Request.QueryString["uid"];
string AmcID = context.Request.QueryString["AMCID"];
string amcdetailid = context.Request.QueryString["AMCDetailID"];
string Problem = context.Request.QueryString["Problem"];
sql = string.Format(@"INSERT INTO [dbo].[CallBooking]
([AMCID]
,[AMCDetailID]
,[DueDate]
,[Problem]
,[ExpectedDate])
VALUES({0},{1},'{2}','{3}','{4}')", AmcID, amcdetailid, DateTime.Now.ToString("yyyyMMdd"), Problem.Replace("'", "''"), DateTime.Now.AddDays(20).ToString("yyyyMMdd"));
dac.executeTQueryEx(sql);
sJSON = "1";
break;
case "updateCall":
uid = context.Request.QueryString["uid"];
sts = context.Request.QueryString["sts"];
string distID = context.Request.QueryString["distID"];
rem = context.Request.QueryString["remarks"];
string completed = context.Request.QueryString["completed"];
string OTP = context.Request.QueryString["OTP"];
string AMCDetailID = context.Request.QueryString["AMCDetailID"];
if (sts == "C")
{
if (dac.returnString("select OTP from AMCDetails where AMCDetailID=" + AMCDetailID) != OTP)
{
sJSON = "0";
break;
}
}
if (!string.IsNullOrEmpty(distID))
{
sql = "updateCallBookingAndDist";
dac = new DataAccessS1();
dac.addParam("@distid", distID);
dac.addParam("@rem", rem);
dac.addParam("@uid", uid);
dac.addParam("@iscomplete", completed);
dac.addParam("@sts", sts);
dr = dac.ExecuteReader(sql, CommandType.StoredProcedure);
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
//sJSON = oSerializer1.Serialize(lstCall);
sJSON = "1";
}
break;
case "amcalert":
uid = context.Request.QueryString["uid"];
sql = @"select P.Name [Product],AD.ProductSlNo,
convert(varchar(10),AD.startdate,103) StartDate,convert(varchar(10),AD.EndDate,103) EndDate,AD.AMCID
from [dbo].[AMCDetails] AD join Master_AMC A on A.AMCID=AD.AMCID
join [dbo].[Master_Party] MP on MP.PartyID=A.PartyID
join [dbo].[Product] P on P.ProductID=AD.ProductID where A.PartyID=" + uid + " and GETDATE() > DATEADD(day,-15,EndDate)";
dac = new DataAccessS1();
dr = dac.ExecuteReader(sql, CommandType.Text);
lstCall = new List <CallList>();
while (dr.Read())
{
objCallList = new CallList();
objCallList.ProductSlNo = dr["ProductSlNo"].ToString();
objCallList.EndDate = dr["EndDate"].ToString();
objCallList.Product = dr["Product"].ToString();
lstCall.Add(objCallList);
}
dr.Close();
oSerializer1 =
new System.Web.Script.Serialization.JavaScriptSerializer();
sJSON = oSerializer1.Serialize(lstCall);
break;
case "secallattended":
break;
case "requestamc":
uid = context.Request.QueryString["uid"];
string partyname = context.Request.QueryString["partyname"];
sql = string.Format(@"INSERT INTO [dbo].[AmcRequests]
([PartyID]
,[ProductName]
,[SerialNo]
,[Model]
,[OtherInfo]
)
VALUES({0},'{1}','{2}','{3}','{4}')", uid, context.Request.QueryString["pname"], context.Request.QueryString["slno"],
context.Request.QueryString["model"], context.Request.QueryString["other"]);
if (dac.executeTQuery(sql) > 0)
{
sJSON = "1";
string body = string.Format(@"Hello,< br /> New AMC request generated.Details below.<br/><strong>Party : </strong>{0}
<strong>Product : </strong>{1}<strong>Model : </strong>{2}<strong>Serial No : </strong>{3}
<strong>Remarks : </strong>{4}", partyname, context.Request.QueryString["pname"], context.Request.QueryString["model"]
, context.Request.QueryString["slno"], context.Request.QueryString["other"]);
Api.Classes.Mailer.SendMail(dac.GetAppSettings("AmcRequestMailID"), "New Amc Request", body);
}
break;
}
context.Response.Write(sJSON);
//context.Response.ContentType = "text/plain";
//context.Response.Write("Hello World");
}