/// <summary>
/// GetdataFromMySQL
/// </summary>
/// <param name="ConString"></param>
public static void GetdataFromMySQL(string ConString)
{
int ID = 0;
int shipment_id = 0;
int TenantId = 0;
string AWBNo = string.Empty;
string InvoiceNo = string.Empty;
string apiResponse = string.Empty;
string apiGenPickupRes = string.Empty;
string apiGenMenifestRes = string.Empty;
string StoreCode = string.Empty;
string ProgramCode = string.Empty;
PickupResponce pickupResponce = new PickupResponce();
pickupResponce.response = new response();
ManifestResponce manifestResponce = new ManifestResponce();
MySqlConnection con = null;
try
{
DataTable dt = new DataTable();
IConfiguration config = new ConfigurationBuilder().AddJsonFile("appsettings.json", true, true).Build();
string ClientAPIURL = config.GetSection("MySettings").GetSection("ClientAPIURL").Value;
con = new MySqlConnection(ConString);
MySqlCommand cmd = new MySqlCommand("SP_PHYGetPickupMenifestDetails", con)
{
CommandType = System.Data.CommandType.StoredProcedure
};
cmd.Connection.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
cmd.Connection.Close();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
ID = Convert.ToInt32(dr["ID"]);
InvoiceNo = Convert.ToString(dr["InvoiceNo"]);
shipment_id = Convert.ToInt32(dr["CourierPartnerShipmentID"]);
TenantId = Convert.ToInt32(dr["TenantId"]);
StoreCode = Convert.ToString(dr["StoreCode"]);
ProgramCode = Convert.ToString(dr["ProgramCode"]);
PickupManifestRequest pickupManifestRequest = new PickupManifestRequest()
{
shipmentId = new List <int> {
Convert.ToInt32(shipment_id)
}
};
try
{
string apiGenPickupReq = JsonConvert.SerializeObject(pickupManifestRequest);
apiGenPickupRes = CommonService.SendApiRequest(ClientAPIURL + "/api/ShoppingBag/GeneratePickup", apiGenPickupReq);
pickupResponce = JsonConvert.DeserializeObject <PickupResponce>(apiGenPickupRes);
if (pickupResponce.status_code == 0 && pickupResponce.pickupStatus == "1")
{
if (pickupResponce.response != null)
{
if (pickupResponce.response.pickupTokenNumber != null)
{
UpdateGeneratePickupManifest(ID, TenantId, ID, "Pickup", ConString);
CommonService.SmsWhatsUpDataSend(TenantId, 0, ProgramCode, ID, ClientAPIURL, "PickupScheduled", ConString);
}
}
}
else
{
ExLogger(ID, InvoiceNo, Convert.ToString(DateTime.Now), StoreCode, pickupResponce.status_code + " : " + pickupResponce.message, apiGenPickupRes, ConString);
}
}
catch (Exception ex)
{
}
try
{
string apiGenMenifestReq = JsonConvert.SerializeObject(pickupManifestRequest);
apiGenMenifestRes = CommonService.SendApiRequest(ClientAPIURL + "/api/ShoppingBag/GenerateManifest", apiGenMenifestReq);
manifestResponce = JsonConvert.DeserializeObject <ManifestResponce>(apiGenMenifestRes);
if (manifestResponce.status_code == 0)
{
if (manifestResponce.status == "1" && manifestResponce.manifestUrl != null && manifestResponce.manifestUrl != "")
{
UpdateGeneratePickupManifest(ID, TenantId, ID, "Manifest", ConString);
CommonService.SmsWhatsUpDataSend(TenantId, 0, ProgramCode, ID, ClientAPIURL, "ManifestGenerated", ConString);
}
else
{
ExLogger(ID, InvoiceNo, Convert.ToString(DateTime.Now), StoreCode, manifestResponce.status, apiGenMenifestRes, ConString);
}
}
else
{
ExLogger(ID, InvoiceNo, Convert.ToString(DateTime.Now), StoreCode, manifestResponce.status_code + " : " + manifestResponce.message, apiGenMenifestRes, ConString);
}
}
catch (Exception ex)
{
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
con.Close();
}
GC.Collect();
}
}
/// <summary>
/// GetdataFromMySQL
/// </summary>
/// <param name="ConString"></param>
public static void GetdataFromMySQL(string ConString)
{
string apiResponse = string.Empty;
string apiGenPickupRes = string.Empty;
string apiGenMenifestRes = string.Empty;
AWBResponce awbResponce = new AWBResponce();
PickupResponce pickupResponce = new PickupResponce();
ManifestResponce manifestResponce = new ManifestResponce();
MySqlConnection con = null;
try
{
DataSet ds = new DataSet();
AWBRequest objdetails = new AWBRequest();
orderDetails orderDetails = new orderDetails();
IConfiguration config = new ConfigurationBuilder().AddJsonFile("appsettings.json", true, true).Build();
string ClientAPIURL = config.GetSection("MySettings").GetSection("ClientAPIURL").Value;
con = new MySqlConnection(ConString);
MySqlCommand cmd = new MySqlCommand("SP_PHYGetOrderdetailForAWB", con)
{
CommandType = System.Data.CommandType.StoredProcedure
};
cmd.Connection.Open();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(ds);
cmd.Connection.Close();
if (ds != null && ds.Tables[0] != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
try
{
if (ds != null && ds.Tables[1] != null)
{
objdetails = new AWBRequest()
{
pickup_postcode = ds.Tables[1].Rows[i]["pickup_postcode"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[1].Rows[i]["pickup_postcode"]),
delivery_postcode = ds.Tables[1].Rows[i]["delivery_postcode"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[1].Rows[i]["delivery_postcode"]),
weight = ds.Tables[1].Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[1].Rows[i]["Weight"]),
orderDetails = new orderDetails()
};
}
orderDetails = new orderDetails
{
Id = ds.Tables[0].Rows[i]["ID"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["ID"]),
order_id = ds.Tables[0].Rows[i]["InvoiceNo"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["InvoiceNo"]),
order_date = ds.Tables[0].Rows[i]["Date"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["Date"]),
billing_customer_name = ds.Tables[0].Rows[i]["CustomerName"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["CustomerName"]),
billing_address = ds.Tables[0].Rows[i]["ShippingAddress"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["ShippingAddress"]),
billing_city = ds.Tables[0].Rows[i]["City"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["City"]),
billing_pincode = ds.Tables[0].Rows[i]["delivery_postcode"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["delivery_postcode"]),
billing_state = ds.Tables[0].Rows[i]["State"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["State"]),
billing_country = ds.Tables[0].Rows[i]["Country"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["Country"]),
//billing_email = ds.Tables[0].Rows[i]["EmailID"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["EmailID"]),
billing_phone = ds.Tables[0].Rows[i]["MobileNumber"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["MobileNumber"]),
shipping_customer_name = ds.Tables[0].Rows[i]["CustomerName"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["CustomerName"]),
shipping_address = ds.Tables[0].Rows[i]["ShippingAddress"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["ShippingAddress"]),
shipping_city = ds.Tables[0].Rows[i]["City"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["City"]),
shipping_pincode = ds.Tables[0].Rows[i]["delivery_postcode"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["delivery_postcode"]),
shipping_country = ds.Tables[0].Rows[i]["Country"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["Country"]),
shipping_state = ds.Tables[0].Rows[i]["State"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["State"]),
//shipping_email = ds.Tables[0].Rows[i]["EmailID"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["EmailID"]),
shipping_phone = ds.Tables[0].Rows[i]["MobileNumber"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["MobileNumber"]),
shipping_is_billing = true,
billing_email = "*****@*****.**",
shipping_email = "*****@*****.**",
payment_method = "Prepaid",
pickup_location = "Test",
channel_id = "633828",
billing_last_name = "",
billing_address_2 = "",
billing_alternate_phone = "",
shipping_last_name = "",
shipping_address_2 = "",
sub_total = ds.Tables[0].Rows[i]["Amount"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["Amount"]),
length = ds.Tables[0].Rows[i]["Length"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["Length"]),
breadth = ds.Tables[0].Rows[i]["Breath"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["Breath"]),
height = ds.Tables[0].Rows[i]["Height"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["Height"]),
weight = ds.Tables[0].Rows[i]["Weight"] == DBNull.Value ? 0 : Convert.ToDecimal(ds.Tables[0].Rows[i]["Weight"]),
StoreDelivery = Convert.ToBoolean(ds.Tables[0].Rows[i]["StoreDelivery"]),
TenantId = ds.Tables[0].Rows[i]["TenantId"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["TenantId"]),
StoreId = ds.Tables[0].Rows[i]["StoreId"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[0].Rows[i]["StoreId"]),
ProgramCode = ds.Tables[0].Rows[i]["ProgramCode"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[0].Rows[i]["ProgramCode"]),
order_items = new List <order_items>()
};
List <order_items> listobj = new List <order_items>();
string ItemIDs = "";
for (int j = 0; j < ds.Tables[2].Rows.Count; j++)
{
if (Convert.ToInt32(ds.Tables[2].Rows[j]["OrderID"]) == Convert.ToInt32(orderDetails.Id))
{
order_items objorder_Items = new order_items()
{
OrderItemID = ds.Tables[2].Rows[j]["ID"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[2].Rows[j]["ID"]),
name = ds.Tables[2].Rows[j]["ItemName"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[2].Rows[j]["ItemName"]),
sku = ds.Tables[2].Rows[j]["ItemID"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[2].Rows[j]["ItemID"]),
units = ds.Tables[2].Rows[j]["Quantity"] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[2].Rows[j]["Quantity"]),
selling_price = ds.Tables[2].Rows[j]["ItemPrice"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[2].Rows[j]["ItemPrice"])
};
listobj.Add(objorder_Items);
ItemIDs += ds.Tables[2].Rows[j]["ID"] == DBNull.Value ? string.Empty : Convert.ToString(ds.Tables[2].Rows[j]["ID"]) + ",";
}
}
ItemIDs = ItemIDs.TrimEnd(',');
/*Check the Pincode Deliver or not*/
HSChkCourierAvailibilty hSChkCourierAvailibilty = new HSChkCourierAvailibilty
{
Pickup_postcode = objdetails.pickup_postcode,
Delivery_postcode = objdetails.delivery_postcode
};
ResponseCourierAvailibilty responseCourierAvailibilty = new ResponseCourierAvailibilty();
responseCourierAvailibilty = CheckClientPinCodeForCourierAvailibilty(hSChkCourierAvailibilty, orderDetails.TenantId, orderDetails.Id, ClientAPIURL);
/*Check the Pincode Deliver or not*/
if (responseCourierAvailibilty.Available == "false")
{
if (orderDetails.StoreDelivery == true)
{
AddStoreResponse(orderDetails.Id, ItemIDs, orderDetails.TenantId, true, ConString);
}
else
{
AddStoreResponse(orderDetails.Id, ItemIDs, orderDetails.TenantId, false, ConString);
}
}
else if (responseCourierAvailibilty.Available == "true")
{
orderDetails.order_items = listobj;
ItemIDs = ItemIDs.TrimEnd(',');
objdetails.orderDetails = orderDetails;
string apiReq = JsonConvert.SerializeObject(objdetails);
apiResponse = CommonService.SendApiRequest(ClientAPIURL + "/api/ShoppingBag/GetCouriersPartnerAndAWBCode", apiReq);
awbResponce = JsonConvert.DeserializeObject <AWBResponce>(apiResponse);
if (awbResponce.data != null)
{
if (awbResponce.data.awb_code != "" && awbResponce.data.courier_name != "" && awbResponce.statusCode == "200")
{
InsertCourierResponse(orderDetails.Id, ItemIDs, awbResponce.data.awb_code, awbResponce.data.courier_company_id, awbResponce.data.courier_name, awbResponce.data.order_id, awbResponce.data.shipment_id, ConString);
CommonService.SmsWhatsUpDataSend(orderDetails.TenantId, 0, orderDetails.ProgramCode, orderDetails.Id, ClientAPIURL, "AWBAssigned", ConString);
if (awbResponce != null)
{
if (awbResponce.data != null)
{
if (awbResponce.data.shipment_id != null)
{
PickupManifestRequest pickupManifestRequest = new PickupManifestRequest()
{
shipmentId = new List <int> {
Convert.ToInt32(awbResponce.data.shipment_id)
}
};
try
{
string apiGenPickupReq = JsonConvert.SerializeObject(pickupManifestRequest);
apiGenPickupRes = CommonService.SendApiRequest(ClientAPIURL + "/api/ShoppingBag/GeneratePickup", apiGenPickupReq);
pickupResponce = JsonConvert.DeserializeObject <PickupResponce>(apiGenPickupRes);
//if (pickupResponce.response.pickupTokenNumber != "")
//{
// UpdateGeneratePickupManifest(orderDetails.Id, orderDetails.TenantId, orderDetails.Id, "Pickup", ConString);
//}
if (pickupResponce.status_code == 0 && pickupResponce.pickupStatus == "1")
{
if (pickupResponce.response != null)
{
if (pickupResponce.response.pickupTokenNumber != null)
{
//UpdateGeneratePickupManifest(ID, "Pickup", ConString, TenantId);
UpdateGeneratePickupManifest(orderDetails.Id, orderDetails.TenantId, orderDetails.Id, "Pickup", ConString);
CommonService.SmsWhatsUpDataSend(orderDetails.TenantId, 0, orderDetails.ProgramCode, orderDetails.Id, ClientAPIURL, "PickupScheduled", ConString);
}
}
}
else
{
ExLogger(orderDetails.Id, orderDetails.order_id, Convert.ToString(DateTime.Now), Convert.ToString(orderDetails.StoreId), pickupResponce.status_code + " : " + pickupResponce.message, apiGenPickupRes, ConString);
}
}
catch (Exception ex)
{
ExLogger(orderDetails.Id, orderDetails.order_id, orderDetails.order_date, Convert.ToString(orderDetails.StoreId), ex.Message, ex.StackTrace, ConString);
}
try
{
string apiGenMenifestReq = JsonConvert.SerializeObject(pickupManifestRequest);
apiGenMenifestRes = CommonService.SendApiRequest(ClientAPIURL + "/api/ShoppingBag/GenerateManifest", apiGenMenifestReq);
manifestResponce = JsonConvert.DeserializeObject <ManifestResponce>(apiGenMenifestRes);
if (manifestResponce.status_code == 0)
{
if (manifestResponce.status == "1" && manifestResponce.manifestUrl != null && manifestResponce.manifestUrl != "")
{
UpdateGeneratePickupManifest(orderDetails.Id, orderDetails.TenantId, orderDetails.Id, "Manifest", ConString);
CommonService.SmsWhatsUpDataSend(orderDetails.TenantId, 0, orderDetails.ProgramCode, orderDetails.Id, ClientAPIURL, "ManifestGenerated", ConString);
}
else
{
ExLogger(orderDetails.Id, orderDetails.order_id, Convert.ToString(DateTime.Now), Convert.ToString(orderDetails.StoreId), manifestResponce.status, apiGenMenifestRes, ConString);
}
}
else
{
ExLogger(orderDetails.Id, orderDetails.order_id, Convert.ToString(DateTime.Now), Convert.ToString(orderDetails.StoreId), manifestResponce.status_code + " : " + manifestResponce.message, apiGenMenifestRes, ConString);
}
}
catch (Exception ex)
{
ExLogger(orderDetails.Id, orderDetails.order_id, orderDetails.order_date, Convert.ToString(orderDetails.StoreId), ex.Message, ex.StackTrace, ConString);
}
}
}
}
}
else
{
AddStoreResponse(orderDetails.Id, ItemIDs, orderDetails.TenantId, false, ConString);
}
}
else
{
AddStoreResponse(orderDetails.Id, ItemIDs, orderDetails.TenantId, false, ConString);
}
}
}
catch (Exception Ex)
{
ExLogger(orderDetails.Id, orderDetails.order_id, orderDetails.order_date, Convert.ToString(orderDetails.StoreId), Ex.Message, Ex.StackTrace, ConString);
}
}
}
}
catch (Exception ex)
{
}
finally
{
if (con != null)
{
con.Close();
}
GC.Collect();
}
}
