public string MojoodiBoreshKhorde(MojoodiInput e) { con.Open(); var golha = new List <Gol>(); var gols = new SqlCommand("select flower_entry.flower_name + ' / ' + flower_colors.flow_color + ' / ' " + "+ flower_colortypes.flow_colortype + ' / ' + flower_formats.flow_format AS gol, flower_entry.id " + "FROM flower_depot.dbo.flower_entry INNER JOIN flower_depot.dbo.flower_colors ON " + "flower_depot.dbo.flower_entry.flower_color = flower_depot.dbo.flower_colors.flowcolor_id INNER JOIN " + "flower_depot.dbo.flower_colortypes ON flower_depot.dbo.flower_entry.flower_colortype = " + "flower_depot.dbo.flower_colortypes.colortype_id INNER JOIN " + "flower_depot.dbo.flower_formats ON flower_depot.dbo.flower_entry.flower_format = " + "flower_depot.dbo.flower_formats.flowformat_id " + "where (flower_entry.customer_name = " + e.Moshtari + " or " + e.Moshtari + " = -1)" + " and (flower_entry.flower_format = " + e.Format + " or " + e.Format + " = -1) " + " and (flower_entry.flower_colortype = " + e.Type + " or " + e.Type + " = -1)" + " and (flower_entry.company_name = " + e.Company + " or " + e.Company + " = -1) " + " and (flower_entry.enter_date between '" + e.EndDate + "' and '" + e.StartDate + "')" + " order by flower_entry.flower_name", con); var r = gols.ExecuteReader(); while (r.Read()) { golha.Add(new Gol() { Id = Convert.ToInt32(r["id"]), GolName = r["gol"].ToString() }); } con.Close(); var list = new List <MojoodiOutput>(); foreach (var i in golha) { con.Open(); var j = new MojoodiOutput { Gol = i.GolName }; var mojoodi = new SqlCommand("select items.item_name as item, cutted from cutted_and_remain " + "inner join items on cutted_and_remain.item_name = items.item_id " + "where flower_id = " + i.Id + " and (item_id in (" + e.Item + ") or '" + e.Item + "' = '-1')", con); var d = mojoodi.ExecuteReader(); while (d.Read()) { j.Itemses.Add(new Items() { Item = d["item"].ToString(), Tedad = d["cutted"].ToString() }); } list.Add(j); list.RemoveAll(x => x.Itemses.Count == 0); con.Close(); } return(new JavaScriptSerializer().Serialize(list)); }
public string GetMojoodiGol(MojoodiInput e) { con.Open(); var golha = new List <Gol>(); var cmd = new SqlCommand("SELECT dbo.flower_colors.flow_color, dbo.flower_colortypes.flow_colortype, flower_entry.id, " + "dbo.flower_formats.flow_format, dbo.flower_customers.customer_name, dbo.flower_companies.company_name " + ", flower_entry.flower_name, flower_entry.flower_code, flower_entry.enter_date " + ", flower_entry.comment FROM dbo.flower_entry INNER JOIN " + "dbo.flower_colors ON dbo.flower_entry.flower_color = dbo.flower_colors.flowcolor_id INNER JOIN " + "dbo.flower_colortypes ON dbo.flower_entry.flower_colortype = dbo.flower_colortypes.colortype_id INNER JOIN " + "dbo.flower_formats ON dbo.flower_entry.flower_format = dbo.flower_formats.flowformat_id INNER JOIN " + "dbo.flower_customers ON dbo.flower_entry.customer_name = dbo.flower_customers.customer_id INNER JOIN " + "dbo.flower_companies ON dbo.flower_entry.company_name = dbo.flower_companies.company_id " + " where (flower_entry.customer_name = " + e.Moshtari + " or " + e.Moshtari + " = -1)" + " and (flower_entry.flower_format = " + e.Format + " or " + e.Format + " = -1) " + " and (flower_entry.flower_colortype = " + e.Type + " or " + e.Type + " = -1)" + " and (flower_entry.company_name = " + e.Company + " or " + e.Company + " = -1) " + " and (flower_entry.enter_date between '" + e.EndDate + "' and '" + e.StartDate + "')" + " order by flower_entry.enter_date", con); var rd = cmd.ExecuteReader(); while (rd.Read()) { golha.Add(new Gol() { Id = Convert.ToInt32(rd["id"]), GolName = rd["flower_name"].ToString(), Color = rd["flow_color"].ToString(), ColorType = rd["flow_colortype"].ToString(), Format = rd["flow_format"].ToString(), Code = rd["flower_code"].ToString(), EnterDate = rd["enter_date"].ToString(), Customer = rd["customer_name"].ToString(), Company = rd["company_name"].ToString(), Comment = rd["comment"].ToString() }); } con.Close(); return(new JavaScriptSerializer().Serialize(golha)); }
public string CustomersItemsCount(MojoodiInput obj) { con.Open(); var gols = new List <Gol>(); var gol = new SqlCommand("SELECT gol.gol, dbo.flower_entry.id FROM dbo.flower_arrange_items INNER JOIN " + "dbo.flower_entry ON dbo.flower_arrange_items.flower_id = dbo.flower_entry.id INNER JOIN " + "dbo.flower_forms_entry ON dbo.flower_entry.id = dbo.flower_forms_entry.flower_id " + "inner join(select flower_entry.flower_name + ' / ' + flower_colors.flow_color + ' / ' " + "+ flower_colortypes.flow_colortype + ' / ' + flower_formats.flow_format AS gol, flower_entry.id " + "FROM flower_depot.dbo.flower_entry INNER JOIN flower_depot.dbo.flower_colors ON " + "flower_depot.dbo.flower_entry.flower_color = flower_depot.dbo.flower_colors.flowcolor_id INNER JOIN " + "flower_depot.dbo.flower_colortypes ON flower_depot.dbo.flower_entry.flower_colortype = " + "flower_depot.dbo.flower_colortypes.colortype_id INNER JOIN " + "flower_depot.dbo.flower_formats ON flower_depot.dbo.flower_entry.flower_format = " + "flower_depot.dbo.flower_formats.flowformat_id) as gol on flower_entry.id = gol.id " + "WHERE(dbo.flower_arrange_items.item_name IN(" + obj.Item + ") or '" + obj.Item + "' = '-1') " + "AND(dbo.flower_entry.flower_format = " + obj.Format + " or " + obj.Format + " = -1) " + "AND(dbo.flower_entry.customer_name = " + obj.Moshtari + " or " + obj.Moshtari + " = -1) " + "AND(dbo.flower_entry.company_name = " + obj.Company + " or " + obj.Company + " = -1) " + "and(flower_forms_entry.arrange_type = " + obj.Arrange + " or " + obj.Arrange + " = -1) " + "GROUP BY dbo.flower_entry.id ,gol.gol", con); var r = gol.ExecuteReader(); while (r.Read()) { gols.Add(new Gol() { Id = Convert.ToInt32(r["id"]), GolName = r["gol"].ToString() }); } con.Close(); var list = new List <MojoodiOutput>(); foreach (var f in gols) { var item = new MojoodiOutput() { Gol = f.GolName }; CalculateItemsByFlowerId(f.Id); con.Open(); var items = new SqlCommand("if (" + obj.Arrange + " = 2) begin select items.item_name , falleh + cutted as falleh" + " from cutted_and_remain inner join items on cutted_and_remain.item_name = items.item_id " + "where (cutted_and_remain.flower_id = " + f.Id + ") and (cutted_and_remain.item_name in" + " (" + obj.Item + ") or '" + obj.Item + "' = '-1') End " + "if (" + obj.Arrange + " = 1) begin select items.item_name , service from cutted_and_remain " + "inner join items on cutted_and_remain.item_name = items.item_id " + "where (cutted_and_remain.flower_id = " + f.Id + ") and (cutted_and_remain.item_name in" + " (" + obj.Item + ") or '" + obj.Item + "' = '-1') End " + "if (" + obj.Arrange + " = -1) begin select items.item_name , total from cutted_and_remain " + "inner join items on cutted_and_remain.item_name = items.item_id " + "where (cutted_and_remain.flower_id = " + f.Id + ") and (cutted_and_remain.item_name in" + " (" + obj.Item + ") or '" + obj.Item + "' = '-1') End ", con); var d = items.ExecuteReader(); while (d.Read()) { item.Itemses.Add(new Items() { Item = d["item_name"].ToString(), Tedad = obj.Arrange == 1 ? d["service"].ToString() : obj.Arrange == 2 ? d["falleh"].ToString() : d["total"].ToString() }); } list.Add(item); con.Close(); } con.Close(); return(new JavaScriptSerializer().Serialize(list)); }